# 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.