I have just read Data Redundancy and Database Design by C.J.Date. It contains some pertinent ideas in my quest for understanding database modelling.
He says that:
…design theory in general can be regarded as a set of principles and techniques for reducing redundancy (thereby reducing the potential for certain inconsistencies and update anomolies that might otherwise occur).
Date 2006, 217
Emphasis in original
I think this objective is really a secondary consideration. We have to determine what we are designing first off. Fortunately, he does come up with a good mantra of “Database Design Is Predicate Design” (Date 2006, 222). He says that an alternative formulation of database design theory is:
…a set of principles and techniques for helping with the business of pinning down predicates…
Date 2006, 222
Italics in original
I think this is more than an alternative formulation. It opens up a different perspective. A database articulates the world-view of a business. The database contains the truth about the world as seen by the business. It is a special type of truth: it is an articulated truth. It is truth is known and spoken about.
Later on in this chapter after he had introduced the concept of the Sixth Normal Form (6NF) (Date 2006, 249), he revisits this alternative formulation to suggest a possible design methodology:
6NF in particular can be seen as assisting in the process of identifying what might be called atomic predicates: that is, predicates that (a) serve as building blocks from which larger ones can be constructed and (b) cannot themselves be broken down into smaller ones. I would like to say too that, for my own part, I find the discipline of insisting that all relvars be in 6NF an increasingly attractive one.
Date 2006, 250
Italics in original
Earlier, I said that a database is the articulated truth about how the business sees the world. Let’s take an example of the ubiquitious Acme Corp.. Their business model is “We sell widgets”. This is a very simple predicate.
Unfortunately, this predicate is not very interesting by itself. Now if we inquire as to what the noun, “widget”, means to the business, we may find that it is really a class of things. If this is so, the business model now becomes two predicates:
- X is a widget; and
- We sell X.
We can now concentrate on what the verb “sell” means to the business. It could mean the business only sells a single widget to each personal customer for cash and immediate pick-up only if the widget is in stock. Let’s further assume that widgets are discrete objects. The business considers a widget to be in stock if the quantity on had is greater than zero. The business will only sell a widget if the sale price is greater than the cost price. Now, we have several more predicates:
- W was a widget which costs C dollars and there are Q units in stock at the time the sale;
- Q was greater than zero at the time of the sale;
- We sold W for P dollars at the time of the sale;
- P was greater than C at the time of the sale; and
- There were Q-1 units of W after the sale was completed.
I had to add two (2) points in time for this transaction:
- at the time of the sale; and
- after the sale was completed.
This is rapidly getting complex. I have now introduced the concept of time into the database model. These predicates can be rewritten as:
- There were Q units of widget W available for sale at time T;
- Widget W cost C dollars at time T;
- Q > 0 at time T;
- P > C at time T;
- We sold widget W for P dollars at time T; and
- If widget W was sold at time T, then there were Q-1 units of widget W at time T+1.
Date, C.J. (2006), ‘Chapter 12: Data Redundancy and Database Design’, in Date, C.J. (Ed.), Date on Database: Writings 2000-2006 (2006), APress: USA, pp. 217-254.