Hermit's Free Library Database Design Cases
Hermit's Free Library Database Design Cases
This is the fifth 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 start-up mail-order company requires a database to run their catalog clothing sales venture.
Sales people will take orders for inventory items over the phone.
Merchandise will be shipped to customers with an enclosed invoice.
The database must accommodate:
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. Here we have placed the fields into four groups. (Note that these field lists are representative and not exhaustive).
When the fields have been grouped into their natural order, each group represents a table. The Orders table represents the header information on an invoice or bill. The Details table represents the line items on the invoice/bill.
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 example we find there is a many-to-many relationship between Inventory and Orders: an inventory item may appear in many orders, and an order may include many items.
We also see that there is a one-to-many relationship between Customers and Orders: a customer may place many orders, but each order belongs to only one customer.
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 database, the Details table serves as a lien between the Inventory and Orders tables, so we place the Primary Keys from the Inventory and Orders tables into the Details table as foreign keys.
Likewise, we place the Primary key from the Customers table into the Orders table as a foreign Key to create their One-to-Many relationship.
Notice the extra key field in the model: ClerkID in the Orders database. That would be a foreign key from another table not included in this design, one containing information on sales force personelle. Placing the primary key from that table in the Orders table would allow us to identify the salesperson for each order. Naturally, the sales force table would have a one-to-many relationship to Orders.
The final result is a relational database with a many-to-many relationship between Inventory and Orders, and a ne-to-many relationship between Customers and Orders.
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.
