151
Chapter 7: Relational Database Design and Creating the Physical Database Schema
7
Best Practice
Granted, knowing when to generalize and when to segment can be an art form and requires a reper-
toire of database experience, but generalization is the buffer against database over-complexity; and
consciously working at understanding generalization is the key to becoming an excellent data modeler.Primary Keys
Perhaps the most important concept of an entity (table) is that it has a primary key — an
attribute or set of attributes that can be used to uniquely identify the tuple (row). Every
entity must have a primary key; without a primary key, it’s not a valid entity.By defi nition, a primary key must be unique and must have a value (not null). The sim-
plest primary key is identifi ed by a single column. For example, a database may contain an
employee table (entity) whose primary key could be the employees’ Social Security number
or a system-generated employee identifi er.For some entities, there might be multiple possible primary keys to choose from: employee
number, driver’s license number, national ID number (ssn). In this case, all the potential
primary keys are known as candidate keys. Candidate keys that are not selected as the pri-
mary key are then known as alternate keys. It’s important to document all the candidate
keys because later, at the SQL DLL layer, they need unique constraints.At the conceptual diagramming phase, a primary key might be obvious — an employee
number, an automobile VIN number, a state or region name — but often there is no clearly
recognizable uniquely identifying value for each item in reality. That’s OK because that
problem can be solved later during the SQL DLL layer.Foreign Keys
When two entities (tables) relate to one another, one entity is typically the primary entity,
and the other entity is the secondary entity.The connection between the two entities is made by replicating the primary key from the
primary entity in the secondary entity. The duplicated attributes in the secondary entity
are known as a foreign key. Informally this type of relationship is sometimes called a par-
ent-child relationship.Enforcing the foreign key is referred to as referential integrity. This type of integrity ensures
that values in the secondary table are contained within the primary table. By applying refer-
ential integrity to your database, you assist in yielding accurate and valid result sets.The classic example of a primary key and foreign key relationship is the order and order
details relationship. Each order item (primary entity) can have multiple order detail rows
(secondary entity). The order’s primary key is duplicated in the order detail entity, provid-
ing the link between the two entities, as shown in Figure 7-2.c07.indd 151c07.indd 151 7/30/2012 4:18:11 PM7/30/2012 4:18:11 PM
http://www.it-ebooks.info