Subtype Key

A subtype inherits its primary key from its supertype. If the supertype is Party, for example, we copy the Party ID down into the primary keys of the subtypes Person and Organization (see Figure 1).

The Challenge

Can a subtype ever have a different primary key than its supertype?

The Response

What makes this question even more challenging is that traditionally we add the primary key in the physical data model yet the subtype structure is purely logical. Gordon Everest tells us more: “The choice of primary key is not dictated by the real world in which there may be multiple candidates for identifying or referencing instances; it is a choice made by the data modeler/implementer. The choice of primary key is made based on a variety of reasons, including implementation. To be sure, we must specify the constraint that the supertype key must be unique and mandatory in the subtype population. It becomes just one possible candidate for the subtype key.” A number of Design Challengers, including George McGeachie, metadata and modeling specialist, and Allan B. Kolber, senior enterprise architect, have similar views. Allan says, “In a proper logical data model, there should only be candidate keys with the primary keys decided in physical design. Many tools force this decision up into the logical.”

If the logical data model does contain primary keys, a majority of Challengers believe strongly that the subtype must have the same primary key as the supertype. The subtype is, by definition, one example of the supertype and, therefore, must have the same primary key. To quote a few Challengers:

• Mike Nicewarner, marketing automation manager: “To be an inheritance, the child has to inherit the parent key.”
• Philip Kelley, database administrator: “I’d think that if it had a different primary key, strictly speaking it would no longer be a subtype.”
• Javier Mazzurco, business intelligence (BI) architect: “The inherited key always represents a subtype uniquely because it’s representing the supertype uniquely.”
• Erik Eckhardt, database developer: “No subtype-supertype relationship exists if the subtype has a different primary key than its supertype.”

Figure 1: Sometimes we rename the subtype primary keys, but it is still the primary key from the supertype. For example, we can rename Party ID in the primary key of Person to Person ID, yet it is still the Party ID.

A number of Challengers use the definition of a subtype to reinforce the importance of matching primary keys. Lee LeClair, senior data modeler, defines a subtype relationship as: “A relationship in which instances of both entities represent the same real or abstract thing. One entity (i.e., the supertype) represents the complete set of things; the other (i.e., the subtype) represents a subclassification of those things.” Spyros Braoudakis, Ph.D., data architect, shares his definition: “A subtype record is the continuation of one supertype record.” Jerry Wiener, data specialist, says the subtype relationship is defined as an “Is A” relationship (as such, the supertype “is a” subtype): “In this example, a ‘Party is a Person’ OR a ‘Party is an Organization.’ Because the subtypes have the same IDENTITY, they require the same IDENTIFIER, hence the same PK [primary key].”

Can a subtype ever have a different primary key than its supertype? Perhaps if you capture history on your logical data model it can. Roopali Doshi, lead BI data modeler, states that sometimes subtype and supertype can have different requirements for storing history, and this can lead to a difference in primary keys. If, for example, the subtype requires full history whereas the supertype only requires a current view, an effective date would be an additional field stored in the primary key of the subtype. Doshi explains, “For example, the primary key for the Person subtype will be Party ID plus Effective Date, whereas for the Party supertype the key remains Party ID.” This can, however, turn the subtype relationship into a one-to-many relationship, which is a different business rule.

If you would like to become a Design Challenger and have the opportunity to submit modeling solutions, please add your email address at www.stevehoberman.com. If you have a challenge you would like our group to tackle, please email me a description of the scenario at me@stevehoberman.com Steve Hoberman is a world-recognized innovator and thought leader in the field of data modeling. He is the author of Data Modeler’s Workbench and Data Modeling Made Simple. You can learn more about his training and consulting services at http://www.stevehoberman.com/.