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.

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.

Primary and foreign key multiplicities

Two general rules may help determine the correct multiplicity on diagrams.

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?

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.