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.

        +-----------+------------+
        |           |            |
 +--------------------------------------+
 | DEPARTMENT | DIRECTOR'S | EMPLOYEE'S |
 |    CODE    |    NAME    |    NAME    |
 +--------------------------------------+
              Repeating Field

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

     +----------------------------+
     |  +-----------+             |  +------------+
     |  |           |             |  |            |
 +-------------------------+  +-------------------------+
 | DEPARTMENT | DIRECTOR'S |  | DEPARTMENT | EMPLOYEE'S |
 |     CODE   |    NAME    |  |    CODE    |     NAME   |
 +-------------------------+  +-------------------------+

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 is only dependent upon CLIENT ID and not ORDER NUMBER.

    +---------+------------+-------------+
    |         |            |             |
+-----------------------------------------------+
| ORDER  | CLIENT   |   CLIENT    |  DEPARTMENT |
| NUMBER |   ID     |    NAME     |             |
+-----------------------------------------------+
     |         | Partial Dependence
     +---------+
   Concatenated Key

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

     +---------+---------+                +--------+
     |         |         |                |        |
+-------------------------------+    +-----------------+
|  ORDER  | CLIENT | DEPARTMENT |    | CLIENT | CLIENT |
|  NUMBER |   ID   |            |    |   ID   |  NAME  |
+-------------------------------+    +-----------------+
               |                          |
               +--------------------------+

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

     +---------+-----------+------------+
     |         |           |            |
 +--------------------------------------------+
 |  KEY  |  FIELD_1  |  FIELD_2   |  FIELD_3  |
 +--------------------------------------------+

Otherwise, it is transitive dependence:

       +----------+----------+---------+
       |          |          |         |
 +--------------------------------------------+
 | EMPLOYEE  | EMPLOYEE |  TASK  | COMPLETION |
 |    ID     |   NAME   |   ID   |    DATE    |
 +--------------------------------------------+
                              |         |
                              +---------+

To correct transitive dependence:

      +----------+----------+           +---------+
      |          |          |           |         |
+------------------------------+  +---------------------+
| EMPLOYEE | EMPLOYEE |  TASK  |  |  TASK  | COMPLETION |
|    ID    |   NAME   |   ID   |  |   ID   |    DATE    |
+------------------------------+  +---------------------+
                         |           |
                         +-----------+