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
Name is unique, you will be violating the
first rule. Finally, you do know that all
instances have non-
NULL names. But are you certain that you will always know the name of ...