This textbook lists 3 options for dealing with a supertype/subtype relationship. However, it doesn't provide a lot of "reasons" to choose one option over the others. What sorts of things should I take into account when designing a supertype/subtype relationship?
The options can be summarized as follows:
- Create an explicit supertype table to abstract the common columns. Sub row references super row PK.
- Create only the subtype tables. Subtypes must all have the "common columns" .
- Create only the supertype table. Supertype must have all of the subtype columns, many of which may well be null if there are lots of subtypes with subtype-specific attributes.
Now, it is obvious to me that options 2 and 3 might have lots of duplicate data if a single super row often has multiple sub rows (e.g. a person is both a student and an employee). It is also clear that option 3 manages to avoid having to join on any queries. Option 1 manages to avoid having to join when we only desire the common columns. Besides this, are there any good reasons to go with one option over the others? Are there any limitations to one or the other options (e.g. if I start out with option 1, and my data model expands, might I find myself in a situation where I am not able to represent my data without extreme effort/ugliness in the supertype/subtype scheme that I've chosen?) What sorts of qualities of my data should I take into consideration when deciding how to implement my supertype/subtype relationship?
