Databases pose an interesting problem. They are used to store large quanitities of similar data. Take a customer database for example. We want to keep a record of all of our customers’ information.
The first step is to determine what data is available to us. Then we look at how to organize our data into tables. There is a lot of discussion on 3rd normal form in terms of database orgainzation. Generally I agree with this, but there are practical reasons for diverging slightly from that in practice even those it is consistent with the concept. That is the use of ENUM fields instead of mini-tables (also referred to as code tables) when the list of values is relatively small.
In our customer database we can group information in the following ways:
This isn't intended as an exhaustive list, but simply one we can use for this example.
Notice anything in this information? Some of the information seems to be similar in format to other information. Shouldn't these be grouped independently? Yes, they should.
So we can start to break this data into logical tables.
OK, that's enough to work with.
Now you are asking yourself, "Didn't this just get really complicated?" The answer is a qualified "yes." OK, so why did we do it then? Aren't we always striving for simplicity? Of course, but sometimes you need to break things apart to put them together more easily.
Imagine for a moment we built a chair out of a solid piece of wood. If any part were to break it would be difficult at best to fix the chair, and the resulting fix would be obviously different from the original chair. This is true in a database as well. We want maintainability and flexibility over pure simplicity. At the same time, we've made each of the original blocks of data simpler as well.
This type of "deconstruction" of the data is commonly referred to as putting things in "Third Normal Form." In a pure implementation of this approach, the contact table "type" field would be a link to another table which contained all of the valid types. Being more practical, and since there is an SQL field type known as an ENUM, I generally use this instead to document small "code tables" that aren't used more than once.
OK, let's step through one example of retrieving an order for a given customer and the detail records for it. We'll specify the customer id and the order id for this example. It is not my intention to explain the details of SQL, especially since there are plently of resources that can do this quite adequately. I'm not going to explain all the details of connecting to a specific database or how to process queries, since those details are both language and database specific. Instead I will use generic function names in the examples so that those details don't get in the way of the discussion at hand.
Given a customer id of say 237 and an order id of 105, let's construct the first query.