The Hermit Hermit's Free Library  Database Design Cases

Social Security Contributions Database

This is the second in a series of database design case studies. This database consists of three tables. It is recommended to begin with the first case study, which illustrates a two-table database.

System Analysis

Design a simple relational database which would track Social Security contributions for workers.

The database must provide the following information:

Fields

The first step in database design is to determine all the necessary fields and segregate them into groups that mirror the natural order of the real world. In this case we have Workers, Employers, and Contributions.

Field names for relational database tables

Tables

When the fields have been grouped into their natural order, each group represents a table.

Diagram of tables in a relational database

Key Fields

Each record in a table must be uniquely identified by its value in the table's key field.

Diagram of relational database tables showing key fields

Relationships

The next step is to identify the relationships between the tables. In this case we have a many-to-many relationship between Workers and Employers. That is to say that Workers may have many employers over their working lives, and Employers can have many Workers.

Diagram of relationships between tables of a relational database

Primary & Foreign Keys

When two tables have a many-to-many relationship we create a third table, often referred to as a lien table because it completes the many-to-many relationship.

Notice that the relationship between both many-to-many tables and the lien is a one-to-many relationship.

As with all one-to-many relationships, we place values from the "one" table's key field into a field of the related records in the "many" table. So in this example the primary keys from both "one" tables (Workers and Employers) are secreted into the "many" table (Contributions) as foreign keys.

Diagram of relationships between tables of a relational database showing primary and foreign keys

The Rationalized Model

The final result is a relational database model. When a database model satisfies all the technical requirements it is said to be rationalized.

The objective of rationalizing a database structure is to eliminate redundancy in order to save disk space and to simplify updates by insuring that every piece of information exists in only one place. The only fields that repeat are the foreign keys.

A rational database model must satisfy these four rules and pass these three tests.



Complete diagram of a relational database