Relational Database Design: Four Rules and Three Tests

A database's structure must reflect the natural organization of the information it contains in the real world, independently of software.

Database structure is created by key fields which create the relationships between different groups (tables) of data. Tables and relationships are what organize the information.

A primary key is the key field which uniquely identifies all the other elements with which it is associated. A primary key must have a one-to-one relationship to all of the elements with which it is associated.

The three types of data relationships

• One-to-One
• One-to-Many
• Many-to-Many

Four rules for creating a proper database structure

1. Each key must be assigned a unique value.
2. All the data elements of a table must have a one-to-one relationship to the value in their key field.
3. For two tables with a one-to-many relationship, place the primary key from the "one" table in the "many" table as a foreign key.
4. For two tables with a many-to-many relationship, create a third table (the lien) and place the primary keys from both many-to-many tables in it as foreign keys, as well as any data elements which are completely dependent upon both the keys.

Three tests for correct database structure

1) Data elements should not be repeated in a file

See how in the following example a DIRECTOR'S NAME would be repeated for all EMPLOYEES with an identical DEPARTMENT CODE.

To correct the problem, remove the repeating field(s) to a separate table:

2) All fields should be dependent upon both parts of a concatenated key

If not, it is partial dependence. In the following table, see how CLIENT NAME should only be dependent upon CLIENT ID and not ORDER NUMBER.

To correct partial independence, place the offending field in a separate database with the key upon which it is wholly dependent:

3) All fields in a file should be related only to the primary key

Otherwise, it is transitive dependence, which is when one field in a table depends on another field, which also depends on the primary key. The resulit of transitive dependence is redundancy.

In the example below, the Completion Date field depends on the Task ID field and both depend on the primary key, Employee ID. This means the contents of the Completion Date field will be repeated needlessly.

To correct transitive dependence, remove the field with transitive dependency (Completion Date) and place it and it's primary key (Task ID) in their own table. Then link the tables using their common key field (Task ID).