[j0ker21m]

Hello guys.

I am needing a database built so I can keep track of which peices are needed for building a part. I cannot figure out how to design the database(s) so that I can have each component listed in its assembly order.

For example, if part 1 takes 6 peices to build, but 3 of them are built in a sub-assembly to make 1 part, how do I get it into databases so that it would read like this:

Part a,b, and c will assemble to make part D. Part D, E, and F will assemble into part 1.

My problem isnt in the coding, its in the design of the database. I cannot figure a logical way to store the information so that I may use queries to sort out the info.

Any help would be appreciated.

[jerry]

Hello...

What you need is a stree structure:

Suggestion Table:

Gen_ID (bigint auto increment, primary)
ParentID (int)
ID (int)
PartName (text)

so basically, there's an entry:

Assume Part A is the main parent, which is made out of part B,C,D
and Part D is made out of part E,F,G

Assuming:
Part A = 1
Part B = 2
Part C = 3
Part D = 4
Part E = 5
Part F = 6
Part G = 7
So, in the database design above, for the main entry, ie: Part A

ParentID = 0 ( signifies that this is the highest parent, no parts above A )
ID = 1
Partname = 'A'

ParentID = 1 ( Assuming Part B is a subpart of 'A', hence parent of 1 (A's ID))
ID = 2 ( B's ID )
Partname = 'B'

ParentID = 1 ( Assuming Part C is a subpart of 'A', hence parent of 1 (A's ID))
ID = 3 (C's ID)
Partname = 'C'

ParentID=1 ( Assuming Part D is a subpart of 'A', hence parent of 1 (A's ID))
ID = 4 (D's ID)
Partname = 'D'

ParentID=4 ( Assuming Part E is a subpart of 'D', hence parent of 4 (A's ID))
ID = 5 (E's ID)
Partname = 'E'

ParentID=4 ( Assuming Part F is a subpart of 'D', hence parent of 4 (A's ID))
ID = 6 (F's ID)
Partname = 'F'

ParentID=4 ( Assuming Part G is a subpart of 'D', hence parent of 4 (A's ID))
ID = 7 (G's ID)
Partname = 'G'

So as you can see, a select statement:

select * from ^^table^^ where parentid=1 ( check for direct subparts of A)
RESULTS: B,C,D
Then repeat the select process...
select * from ^^table^^ where parentid=4 ( check for direct subparts of D)

as you can see, you've gotten a tree structure. Now, all you need to do is lots of programming to get things together. Please consider General Scripting if you need web development services some day.