Four design rules and three tests that result in a proper relational database design every time.
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.
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 | +-------------------------+ +-------------------------+
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 | +-------------------------------+ +-----------------+ | | +--------------------------+
+---------+-----------+------------+ | | | | +--------------------------------------------+ | 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 | +------------------------------+ +---------------------+ | | +-----------+