Managing & Using MySQL, 2nd Edition

The Unique Identifier

Before discussing relationships, we need to impose one more rule on entities. Each entity must have a unique identifier—we’ll call it the ID. An ID is an attribute of an entity that meets the following rules:

• It is unique across all instances of the entity.

• It has a non-NULL value for each instance of the entity for the entire lifetime of the instance.

• It has a value that never changes for the entire lifetime of the instance.

Identifier selection is critical because the identifier is also used to model relationships. If, after you’ve selected an ID for an entity, you find that it doesn’t meet one of the above rules, this could affect your entire data model.

Novice data modelers often make the mistake of choosing attributes that should not be identifiers and making them identifiers. If, for example, you have a Person entity, it might be tempting to use the Name attribute as the identifier because all people have a name and that name never changes. But names do change. What if the person marries? What if the person decides to legally change his name? What if you misspelled the name when you first entered it? If any of these events causes a name change, the third rule of identifiers is violated. Worse, is a name really ever unique? Unless you can guarantee with 100% certainty that the Name is unique, you will be violating the first rule. Finally, you do know that all Person instances have non-NULL names. But are you certain that you will always know the name of ...