The Hernandez Process In A Nutshell:
One of the best database design books of our time is:
Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design By
Michael J. Hernandez.
Unfortunately, Hernandez goes into such great detail at times that the overall picture is lost. The following is a list of the major steps in the process. This list should not be use in place of reading the book. It is meant only as an adjunct to it.
- Create a narrative that accurately and in some detail describes the business
- Double underline each Subject and Direct Object in each sentence
- Single underline all the rest of the nouns that describe the subject or direct object
- Make a list of the Subjects. Try to determine which are duplicates or are not pertinent. This will be your Subject List
- Make a list of the other nouns. Try to determine which are duplicates or are not pertinent. This will be your Preliminary Field List (PFL)
- Group the fields in the PFL into logical groupings. Do each of these groupings have a corresponding value in the Subject list? If not, you missed a subject so you should add it.
- Go through the revised Subject list to see if there is any data that you should be storing about that subject that you are not. If so, add it to the field list.
- Place all the Subjects across the top of a sheet of paper and write the corresponding fields below them, check them off the list as you do.
- Now look at your tables and fields and compare them to the Ideal Table and Ideal Field:
Elements of the Ideal Field
--------------------------------------
It represents a characteristic of the subject of the table.
It contains only a single value
It cannot be deconstructed into smaller components
It does not contain a calculated or concatenated value
It is unique within the entire database structure
It retains all of its characteristics if it appears in more than one table.
Elements of the Ideal Table
----------------------------------------
It represents a single subject
It has a primary key
It does not contain multipart fields
It does not contain mulivalued fields
It does not contain calculated fields
It does not contain unnecessary duplicate fields
It contains only the minimum redundant data
- Create Primary keys and identify any Alternate Keys (see Hernandez)
- Note: Most tables will have Artificial Primary keys (Autonumber in MS Access).
- Alternate keys are used for Unique Indexes.
- Identify relationships between the tables. Be sure you can identify it in both directions:
Each Customer can have One or More Orders
Each Order can pertain to One and Only One Customer
- Resolve any Many-to-Many relationships with the addition of a Linking table and two One-to-Many Relationships.
Order >----------------------<
Products
(Each Order can have Many Products)
(Each Product can be on Many Orders)
Order ----------< OrderProducts >---------Products
- Create your relationships, put the Primary Key of the table on the "One" side into the table on the "Many" side.
One-To-Many
Customer Order
--------------- ----------------
CustomerID (pk)------|
OrderID (pk)
CustomerName |-------< CustomerID (fk)
...other fields OrderDate
...other fields
Many-To-Many
Order OrderDetails Products
--------------- ----------------- ----------------
OrderID (pk)--------<
OrderID (cpk)(fk) |----
ProductID
OrderDate
ProductID (cpk)(fk)>--| ProductName
...other fields ...other fields