Is stability an important property for a candidate key on the logical data model?

Is stability an important property for a candidate key on the logical data model?

A candidate key is an attribute (or set of attributes) that identifies an entity. A candidate key is either a primary key or an alternate key. So for example, in the following Student entity, there are two candidate keys: a primary key on Student Number and an alternate key on Student First Name, Student Last Name, and Student Birth Date.

In my data modeling classes, I stress that a candidate key must have three properties:

  • Unique. So if we have 100 students we will have 100 unique Student Numbers and 100 unique combinations of Student First Name, Student Last Name, and Student Birth Date. For example, we cannot (on this model) have two students with the same first and last name born on the same date.
  • Mandatory. A candidate key must always be populated and have a value, that is for example, the Student Number cannot be null (empty).
  • Stable. We cannot update the value of a candidate key. For example, we cannot change Student Number 123 to a different value such as 124.

Someone challenged me recently on the stability property. His comment was, if a logical data model represents a point in time perspective (that is, it is timeless), why is the stability property relevant?

In our physical design, we manage how data changes over time, such as a slowly changing dimension managing changes to the Student Last Name, but is stability important on the logical? Why or why not?

What are your thoughts?

 

2 Comments

  1. Kent Graziano 6 months ago

    That is a really interesting question for a logical model for sure! I have usually taken the stance that is should be reasonably stable – meaning it has a low propensity for change. Knowing that in the real world data does get mis-entered on occasion and over time may change so trying to find a key that never, ever, no mater what changes, could lead to an analysis nightmare. I also think that while in the system (physical implementation) the key value might occasionally change, the more important question (to me) is whether or not from business perspective it should be changeable. If the business says it should NOT be changeable then the business definition is indeed stable at the logical level. Right? So the fact that over time the system may allow it to change under certain conditions is a physical implementation (master data management?) issue to figure out how to map the changed keys to the original. (In data vault we do that with a same-as-link, in 3NF it would be an associative M:M table).

  2. David Jaques-Watson 6 months ago

    I would say the student is right, if the data model represents a data set like an Enterprise Data Warehouse, whose *primary* reason for existence is to allow “over time” or “longitudinal” analysis – and is therefore designed that way.

    That is, most EDW entities are designed with multi-part keys: the first part being a “stable” attribute inherited from the source system (like Student Number above) and the second part being Date of Effect or similar. The concatenated key is, strictly speaking, “unstable” because it changes over time.

    But that’s because it *represents* a point or range in time. 😉

Leave a reply

Your email address will not be published. Required fields are marked *

*