This is the sixth 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 magazine distributor has approached Databased Advisors for a proposal to create a database for its subscription service.
The distributor needs two reports:
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 organized 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.
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.
We determine that there is a one-to-many relationship between Publishers and Magazines: a publisher can publish many magazines, but every magazine is published by only one customer.
We can also see that there is a many-to-many relationship between Subscribers and Magazines. A subscriber can subscribe to many magazines, and each magazine can be subscribed to by many subscribers.
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 example, the Subscriptions table serves as a lien between the Subscribers and Magazine tables, so we place the Primary Keysfrom the Subscribers and Magazine tables into the Subscriptions table as foreign keys.
Likewise, we place the Primary key from the Publishers table into the Magazine table as a foreign Key to create their One-to-Many relationship.
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.