Null Alternate Key?
Design Challenge
Information Management Magazine, September 2007
An alternate key is a unique identifier for an entity instance that has not been selected as the primary key. In the following Order entity, for example, Order_Identifier is the primary key and Order_Number is the alternate key (see Figure 1).
Figure 1: Design Challenge Example
Advertisement
Order_Number is the alternate key, yet it is defined on this model as null (meaning it could be left empty).
The Challenge
Can an alternate key ever be null? If yes, under what situations can it be null? If no, why not?
The Response
With close to 100 responses to this challenge, I was very impressed that the responses were almost equally split between those who said you could have a null alternate key (51 percent) and those that said you cannot (49 percent).
An Alternate Key Can Never Be Null
Mike Vaughan, data architect, says, "If you define unique as meaning every record has a distinct value, then the key can never be allowed to be null as this indicates a value does not exist." Paula Stone, information architect, agrees, "By definition, a key is a value through which an instance can be uniquely identified, and by definition, a null is no value at all." Warren Cotton, modeling project coordinator, reminds us that a null alternate key could pose data retrieval issues if it is also the business natural key, "When loading the database, the natural key may be used to determine whether this is a new instance or an update to an existing instance. Also, end users may want to look up orders by the natural key, which should be unique. A nullable natural key cannot be unique."
An Alternate Key Can Be Null in Certain Situations
There were several situations where Challengers felt an alternate key could be null, including for data quality issues, states and integration.
Data quality issues. A number of Challengers, including Anders Lindell, data architect, and David Reynevich, systems developer, mentioned that poor data quality in the source system or weak business processes can lead to a null alternate key. Hilary Spezzaferro, data modeler, mentions in this situation that additional values such as No Order or Not Applicable might alleviate the issue.
States. Barb McCuaig, data analyst, says that an alternate key may not yet be generated, depending on the entity's state in its lifecycle. "For example, an order number may not be generated until the order reaches the fulfillment process; therefore, it does not exist at the point the order is created." Ralph Nijpels, business analyst, says, "An alternate key can be null only if, in the business perspective, it does not exist. For instance, a car in a garage that does not have a license plate yet or, in air cargo, a shipment for which space has to be reserved but for which no air waybill number has been assigned yet." Rahmatullah Mohammed, business intelligence consultant, and Bob Mosscrop, enterprise data architect, both gave Social Security number as an example, which may not be known initially and then is later populated.
Integration. When different sources are brought together into a single model, such as for a data warehouse, it is possible that what was a unique alternate key in the source system might contain null values in the integrated view. Both Alex Kalmykov, database architect, and Ben Ettlinger, lead data administrator, have experienced this situation. If Order and Credit are folded up into the more generic Event entity, for example, Order Number will contain nulls when the Event instance is a Credit.
Gordon Everest, professor emeritus, suggests that there is more than one definition of alternate key and recommends modeling the users' world as accurately as possible and learning how a particular database defines the term. "If your DBMS or modeling scheme includes the notion of alternate key, you must see how it is defined." Johnny Gay, data analyst, also suggests re-evaluating the alternate key definition, "We may have a wolf in sheep's clothing in the case where a nonunique index is labeled an alternate key that need not be unique and can be null."
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 one of the world's most well-known data modeling gurus. He taught his first data modeling class in 1992 and has educated more than 10,000 people about data modeling and business intelligence techniques since then. Steve is known for his entertaining, interactive teaching and lecture style (watch out for flying candy!), and organizations around the globe have brought Steve in to teach his Data Modeling Master Class, which is recognized as the most comprehensive data modeling course in the industry. Steve is the author of "Data Modeling Made Simple," "Data Modeler’s Workbench" and "Data Modeling for the Business (Technics Publications). He is the founder of the Design Challenges group and inventor of the Data Model Scorecard.
For more information on related topics, visit the following channels:
