Hermit's Free Library Database Design Cases
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.
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.
When the fields have been grouped into their natural order, each group represents a table.
Each record in a table must be uniquely identified by its value in the table's key field.
The next step is to identify the relationships between the tables. In this case we have two many-to-many relationships.
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.
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.
