FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Null Alternate Key?

Design Challenge

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

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 has worked as a business intelligence and data management practitioner and trainer since 1990. He is a Certified Business Intelligence Professional (CBIP), having achieved mastery level certification in data analysis and design. He is a popular and frequent presenter at industry conferences, both nationally and internationally.  Hoberman is a columnist and frequent contributor to industry publications, as well as the author of  Data Modeler's Workbench and Data Modeling Made Simple (available for purchase through the DM Review bookstore). He is the founder of the Design Challenges group, inventor of the Data Model Scorecard and a recognized innovator and thought leader in the field of data modeling. He can be reached at me@stevehoberman.com.

Graeme Simsion's latest book is out! Data Modeling Theory and Practice. Here's a link where you can read more about the book and purchase it at a discounted price.

For more information on related topics, visit the following channels:



Industry Vendors