Database: Heuristics for relationship schemas
Here are some heuristics, rules of thumb, that might help you
in the relational schemas and the design of your database.
Primary key multiplicity
The multiplicity at the primary key end of a FK-PK
relationship must either be 1 or 0/1. There are two common cases
where it can be 0/1.
- When implementing a superset-subset relationship as between
Pilot and Employee (all pilots are employees, but not all
employees are pilots).
- When the foreign key may be NULL. For example, not every
student has an advisor.
A bridge table is required for many-to-many relationships
Each bridge table record is one instance of a relationship between
two entities (an enrollment is one relationship between a student and a section).
Each record consists of a pair of keys for each of the two
tables, possibly with other information that is relevant to the
relationship (eg, the grade a student receives for that enrollment).
Here are two common types of bridge tables.
- Unique relationships. For example, there can only be one
enrollment of a particular student in a particular section.
The pair of forign keys in the bridge table can be used
as a unique primary key.
- Multiple relationships. For example, there may be more than
one comment by a person about a restaurant. Even the
comment text may be the same. In this case you will need
an additional field to function as the primary key (or
something like a count that can be combined with the foreign keys to
form a primary key)
Primary and foreign key multiplicities
Two general rules may help determine the correct multiplicity on diagrams.
- in a 1:N relationship, the foreign key is always on the "many" side of a relationship
- A primary key is always on the one side.
Repeating attributes go into separate table
Normalization requires that repeating attributes be placed in a separate
table which contains the attribute value and a foreign key (the many side)
referring back to the primary key (the one side).
A foreign key field implies maximum multiplicity of one
A foreign key in a table means that the entity can
have a maximum of one relationship to the entity it references.
Eg, if a student_id field was added to the Section
table, each section could only be linked to one student! Wrong.
Each Section record has an instructor id so it can have
only one instructor. That's OK.
Field or table?
How do you decide if something should be a field
or a table?
- Does it have attributes? If so, it might be a table. If not (like "year")
it should probably be a simple attribute.
- Does it have a one-to-one relationship with the record? If yes, even if it has
its own attributes, it belongs as multiple fields, not in a separate table.
Example: Person-License [TO DO].
- Is it a repeating value? If yes, the rules for a relational database
require a separate table.
- Is it too long to put into a record? If so, it can be put
into a separate look-up table and a short key can be stored in the record.
Use look-up tables for common, long attributes
Sometimes it is better to store an attribute in a separate table and put a key
in the original record. For example, in a DVD colletion database
should the rating be a table or a field? Because there is nothing
you want to know about ratings, it should be a field.
However, it is reasonable to have a lookup table that can be used
to translate a short rating code, like "NC-17" into an expanded explanation.
- If the key value is shared by many records. Eg, movie rating example.
- If a key is too long to efficiently store in a table. Make up a short
key and put the long version in a separate table.