Blue Tree Industries is a company that makes many different sorts of widgets. They want to be able to keep track of what sorts of widgets they make, orders for widgets and customers.
In designing a database the first thing you want to do is figure out what you are trying to keep track of. In the case of of Blue Tree this is widgets. BT wants to keep track of what widgets they make, what color these widgets are, how big they are, how many they have in stock and the price they charge people for that sort of widget.
in SQL this would look as follows.
# The Widget table. # # Detailed information on a widget. Linked to by Purchase_Order_Item # by way of the widget_id field. Linked to Widget_Color by way of # the widget_color field, and to the Widget_Size table by way of the # widget_size field. CREATE TABLE Widget_Table ( widget_id MEDIUMINT(8) NOT NULL AUTO_INCREMENT, widget_name CHAR(60) NOT NULL, widget_color_id MEDIUMINT(8) NOT NULL, widget_size_id MEDIUMINT(8) NOT NULL, widgets_on_hand SMALLINT NOT NULL, widget_price FLOAT(8,2) NOT NULL, commission_percent FLOAT(2,2) NOT NULL, PRIMARY KEY (widget_id), KEY (widget_name), KEY (widget_color_id,widget_size_id) );The first line tells the mysql SQL engine that we are defining a table called Widget_Table. The next six lines define the fields that the table contains, what sort of data goes in them, and what attributes these fields have.
Before you can create this table you will have to create an empty
database. In mysql this is done by way of the mysqladmin
prompt> mysqladmin create Widget-DBOne of the more basic concepts in good relational DB design is that you should never store redundant data. In the case of the Widget_Table this is reflected in the following fields. widget_color_id and widget_size_id. These two fields could have been strings. Instead we make them pointers to other tables that will contain one valid entry for each possible value that they each may contain.
This is done for two reasons:
I'll explain the second reason by talking a bit about my personal experience. I've designed several small to medium sized DB's myself, and have worked on projects that have involved very large and complex DB's. One lesson I've learned from all this is that you want to put as much of the complexity in a given client/server application into the server. Depending on the client to enforce data access restrictions, data consistency, etc ends up being a very bad idea in the long term. You end up having to massage data in strange ways to make it meaningful, ("What do they mean when they say the color is sea green? How do I map that?") You end up with situations where clients get written that do not conform to whatever standard you put in place, and things break, etc. and so on. Let's just say it can get really ugly really fast.
OK, let's get back to talking about the Widget_Table.
The widget_id field is a medium (3 byte) sized integer. It has the special attributes NOT NULL and AUTO_INCREMENT. NOT NULL is ANSI SQL standard and specifies that when somebody enters widget information into this table they must give some value for this field. Of course, if a default value has been specified, that value will be used when no value is given. IE, default values remove the necessity to assign a value to a field with the NOT NULL attribute.
AUTO_INCREMENT is a mysql specific attribute. If you insert a zero into this field mysql will automatically assign a value that is one higher than the highest previous value assigned to this field in this table. This is a simple straightforward method for generating unique identifiers for new widgets as they are entered into the table. Since mysql automatically assigns default values to fields that are declared NOT NULL, you don't actually have to include the AUTO_INCREMENT field in you insert statement, as it will end up getting the default value (zero) and thus the index will be "magically" generated.
We also define several keys. When you assign a field the AUTO_INCREMENT attribute you must also define this field as the primary key. You can only have one primary key per table. By extension, only one field per table may have the AUTO_INCREMENT attribute.
We also create secondary indexes by use of the word KEY. Indexes significantly increase the speed of queries and joins. Indexes may include more than one field. If you have an index that includes more than one field you do not need to create indexes on the individual fields that make up the composite index. The mysql query optimizer will "Do the right thing." and take advantage of the information in the composite index.
OK, we've defined what the Widget_Table looks like, Now we need
some way of keeping track of who has ordered our widgets. For this purpose
we define the Purchase_Order table.
# The Purchase Order table. # # customer_id links us to the Customer_Table # Where we can get more information about the customer. # # To allow for N items on a Purchase order we have to # have another table called Purchase_Order_Item that # we link to be way of purchase_order. CREATE TABLE Purchase_Order ( purchase_order MEDIUMINT(8) NOT NULL AUTO_INCREMENT, customer_id SMALLINT NOT NULL, order_date DATE NOT NULL, due_date DATE NOT NULL, close_date DATE NOT NULL, status_code TINYINT(2) UNSIGNED NOT NULL, last_action_date TIMESTAMP, PRIMARY KEY (purchase_order), KEY (customer_id,order_date,status_code) );We assume that a customer is going to order from us more than once. For this reason rather than entering the customers address and name info into this table we assign them a unique identifying number that links us to an entry in a table that contains this information. We'll call this the Customer table. We'll talk about it a bit later. You'll also notice that we do not have any mention of widgets in this table. This is because a purchase order can be for multiple widget types. We could just define some fixed number of widget types that could be ordered on a given purchase order, but that wouldn't be very flexible. Instead we define a field called purchase_order which will contain a unique number for each purchase order. Then we define another table which will contain one entry for each line item a particular purchase order has.
Also of note is last_action_date. This field is of type TIMESTAMP. Fields of this type are automatically updated whenever an INSERT or UPDATE is performed on them. This makes it easy to tell when a record was last changed.
Now for the Purchase_Order_Item table.
# The Purchase_Order_Item table. # # Since there can be more than one item on a purchase order # we need a table that contains a single item. We link back to # The main Purchase_Order table by use of the purchase_order field. # # We can also link back to the Widget_Table table by use of the # widget_id field. # # Last of all we link to the Status table by way of the status_code field. CREATE TABLE Purchase_Order_Item ( purchase_order SMALLINT NOT NULL, order_sequence SMALLINT NOT NULL, widget_id MEDIUMINT(8) NOT NULL, quantity SMALLINT(4) UNSIGNED NOT NULL, status_code TINYINT(2) UNSIGNED NOT NULL, order_date DATE NOT NULL, item_due_date DATE NOT NULL, deliver_date DATE NOT NULL, last_action_date TIMESTAMP, PRIMARY KEY (purchase_order,order_sequence), KEY (widget_id), KEY (status_code,order_date) );In the Purchase_Order_Item table we keep track of all line items in a purchase order. We need to know what item was ordered, how many of them were ordered, what the status of this particular line item is and when it was ordered. BT likes to have the flexibility of adding items to purchase orders while they are still open. For this reason we have order,item and delivery date fields for individual line items.
OK, now lets look at the Customer table.
# The Customer table. # # We need to know where to send those widgets. # # Links back to Purchase_Order by way of customer_id. CREATE TABLE Customer ( customer_id SMALLINT NOT NULL AUTO_INCREMENT, customer_name VARCHAR(80) NOT NULL, customer_contact VARCHAR(80) NOT NULL, customer_address VARCHAR(80), customer_city VARCHAR(80), customer_zip VARCHAR(10), customer_phone VARCHAR(20), customer_fax VARCHAR(20), PRIMARY KEY (customer_id), KEY (customer_name) );This is fairly straight forward. We need to know the name of the company, where to deliver the widgets, who to contact at the customer site if we have any questions, what their phone number is and where we can fax them information.
When you have customers, you have sales people. And when you have sales people you need to keep track of them. Who knows what they would get up to if you didn't. :-)
We'll end up needing two tables for this, one to keep track of BT's sales people, and another to keep track of the customers these sales people have assigned to them. There is already a field in the Widget table that will allow us to figure commissions. We'll talk about that in the next section of this tutorial.
In naming this table we can have a bit of fun at the expense of
the sales people. # The Sales_droid table. # # Keep track of the people
who sell the widgets. # CREATE TABLE Sales_Droid ( sales_droid_id SMALLINT
NOT NULL AUTO_INCREMENT, sales_droid_first_name VARCHAR(80) NOT NULL, sales_droid_last_name
VARCHAR(80) NOT NULL, sales_droid_phone VARCHAR(20) NOT NULL, PRIMARY KEY
(sales_droid_id) ); OK, We're getting there. Three more tables to go. These
ones will be simple.
# The Status table. # # Table to contain all valid status codes. # # Links to to Purchase_Order_Item and Purchase_Item by way of status_code. CREATE TABLE Status ( status_code TINYINT NOT NULL AUTO_INCREMENT, status_text VARCHAR(80) NOT NULL, PRIMARY KEY(status_code) );The Status table is very simple. We need a unique numeric ID assigned to a short text field that contains the text of the status code.
# The Widget_Color table. # # Table to contain all valid color codes. CREATE TABLE Widget_Color ( widget_color_id TINYINT NOT NULL AUTO_INCREMENT, color_text VARCHAR(80) NOT NULL, PRIMARY KEY(widget_color_id) ); # The Widget_Size table. # # Table to contain all valid color codes. CREATE TABLE Widget_Size ( widget_size_id TINYINT NOT NULL AUTO_INCREMENT, size_text VARCHAR(80) NOT NULL, PRIMARY KEY(widget_size_id) );The Widget_Color and Widget_Size tables are almost identical to the Status table. Only the names have been changed to protect the innocent.
Now we need some data for our database.