I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes. This is the list of entities and their attributes:
Piece (PIECE_ID, Description) In_house (PIECE_ID) Bulk (PIECE_ID, Location) To order (PIECE_ID, Lead time) Purchased (PIECE_ID, Cost) Order (OID, Order date) Supplier (SID, Name, Postcode, City ) Supplier piece (SPID, Price, description)
To clarify, Piece is the supertype with two subtypes, In_house and Purchased. In_house then has two subtypes, Bulk and To Order.
My first instinct was to keep a table Piece(PIECE_ID, description), another one for Purchased(PIECE_ID, cost), and then delete the In_house one and keep only the subtypes as Bulk(PIECE_ID, Location) and To_order(PIECE_ID, Lead Time).
First question: is this better as opposed to keeping the In_house table and put the Bulk and To_order info all in that table, generating some NULL values?
Also, how does the system know that there can't be any repeated PIECE_ID in the Purchased, Bulk and To_order tables? Do I need to create a separate ID for these individual tables or can they just the foreign key for the Piece table?
Finally, the Purchased table has a 1:N obligatory on both relationship with Supplier part table. Therefore, I have to put the primary key of the Purchased table in the Supplier piece table. If the primary key on Purchased is just PIECE_ID, how can it associate that those PIECE_ID are from the Purchased table and not from the other tables that have PIECE_ID as a primary key?
Please let me know in comments if clarification is needed.