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.


2 Answers 2


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:

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

( 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)

( 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).


An important deciding factor would be business preference for performance vs code complexity.

If performance is critical, then code-complexity takes a back seat. In this case, you probably want to ensure you look at keeping critical rows as compact as possible, by not having little-used columns in the main table.

If code-simplicity is deemed of paramount importance, performance takes a back seat, allowing a much wider range of possibilities.

SQL Server has a little-implemented feature know as Sparse columns that allow columns with a large ratio of null-to-non-null values to be more storage-efficient. So, if 90% of pieces are "in-house", then you can add sparse columns for the 10% that are "purchased" to the main table, and not really worry too much about the performance impact of reading those rows, while ensuring all those NULL values are as space-efficient as possible.

As is normally the case, the devil is in the details, and vastly dependent on the requirements for the specific business.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.