Junction Table

[troachjr]

I have a question about database design. Let me explain what I have so far. I need a database to work with multiple plants that have multiple systems at each plant with equipment in each system. Each system could and does have in many cases the same ID name. I created the junction table with index on System ID and Units ID to make the index unique. No 2 systems can have the same id number and same unit id number. What I have done so far is as follows:

Plant Table
Plant ID PK
Plant Name, etc...

System Table
System ID PK autonumber
Plant ID FK to Plant Table
System Name, etc..

Units Table
Unit ID PK autonumber
System ID FK
Unit Name, etc...



SystemUnits Junction Table
SystemUnitsID PK autonumber
System ID FK to System Table
Unit ID FK to Units Table
Zone ID

The child tables will store data about each type of equipment, serial number, model, etc. The Child table will reference the System Units table and have its own index of the autonumber type. I then have information that is relevant to the child table, of which I could call the grandkids table, i.e., motor info, electrical source etc. Would it be proper to reference the child table index or better to reference the junction table?

[recyan]

Till some one comes along,
Are statements a---e, correct. If not pls correct them.
a) Plants.
b) One Plant can have One or Many Systems.
c) One System can belong to One or Many Plants.
d) One System can have One or Many Units.
e) One Unit can belong to One or Many Systems.
...........
________________________________

Each system could and does have in many cases the same ID name.

1) Can you elaborate on above ?
2) What is the relationship between Equipment and Unit?
3) Elaborate on Child table and Grand-child table?
If you can post a few dummy data showing the relationships between what you have mentioned, it should perhaps be easier ( perhaps like what are plants, what are systems, what are units,...).

Note : It could perhaps be me only who is unable to get what you have explained, but your answers might enable others to help faster.

Thanks