Relationships

Topic

New Reply

I have an Orders database I am developing. A customer can order many parts. Two types of parts are used. Part A and B. An order can contain many of either. However, Part B contains many of Part A. Part A cannot contain any Part B.

I’m stumped as to how to relate the tables. I can make a many to many so one Part A can be used in many Part B, and one Part B can contain may Part A. However, I’m not sure how to create a relationship so an order can contain a Part B or a Part A.

I hope this makes sense. I’ve attached a screen shot of my relationships without the missing join line to the OrderDetail table.

Thanks in advance

Reply | Quote

Replies

Your screen shot does not include a Parts Table unless it is called something else.

To deal with Part Bs containing many Part As I would have a many-to-many relationship between the Parts Table and itself.

In tblPartsComponents PartIDA would contain the PartID for a type A part, PartIDB would hold the PartID for a Type B part, and the quantity field would say how many of the As go into a B.

Reply | Quote

Thanks. I sometimes run out of ideas – never thought of having a table used twice. I’ll give it a try…

(My screen shot has different table names, but your suggestions is fine.)

Reply | Quote