Sql-server – Dealing with subtypes and supertypes in Database design

database-designforeign keyprimary-keysql serversubtypes

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.

Best Answer

Not sure I understand all of your questions, but one fairly common model for super/sub-type is to add a classifier attribute to your supertype:

CREATE TABLE super
( xxx_id ... not null primary key
, classifier int not null
,   unique (classifier, xxx_id)
,   check (classifier in (1,2,...))
)

CREATE TABLE sub1
( xxx_id ... not null primary key
, classifier int default 1 not null
,    check (classifier = 1)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

CREATE TABLE sub2
( xxx_id ... not null primary key
, classifier int default 2 not null
,    check (classifier = 2)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

Now the classifier in super guarantees that for example 1 can't be located in sub2 (that it exist in any subtype is handled by a transaction).

However, this model is not fully practical if the classifier changes, so this is more of a food for thought answer (but it is too long to be a comment).

Related Question