The Hermit Hermit's Free Library  Database Design Cases


This is the seventh in a series of database design case studies. The database in this example is constructed of four tables. It is recommended to begin with the first case study, which illustrates a two-table database.

System Analysis

A "Clinician" could be a doctor, a nurse, a veterinarian, or even a mechanic.

A "patient" could be your Uncle Fester, a cow, or a car or roto-tiller,.

A "procedure" could be a checkup or gall bladder operation, a calve delivery, or an oil change.

What's required is a database which tells us who did what to whom at what time on what day, for how long, and for how much.

Design and diagram a database structure using standard notation.


As usual, 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 Clinicians (or Workers), Patients, Procedures, and Appointments.

Field names for relational database 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


The next step is to identify the relationships between the tables. In this case we have two many-to-many relationships.

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.

In this case, the Appointment table serves as a lien between the Clinicians and Patients tables, and also between the Patients and Procedures tables.

Notice that the relationships between both sets of many-to-many tables (Clinicians & Patients, and Patients & Procedures) and the lien table (Appointments) are one-to-many relationships.

To complete the 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. In this example we put the primary keys from all three "one" tables (Clinicians, Patients, Procedures) into the lien table (Appointments) 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 with three tables in two many-to-many relationships with a single lien table. 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.

Every rational database model must obey these four rules and pass these three tests.

Complete diagram of a <img class=