MySQL Appendix B (Sample Database and Tutorial)

Documentation Version: 0.95
MySQL Version: 3.20.29

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)
);

Before you can create this table you will have to create an empty database. In MySQL this is done by way of the mysqladmin program.

prompt> mysqladmin create Widget-DB

This is done for two reasons:

• Consistency
• Centralization of complexity

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 another index on the first field in 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)
);

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)
);

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)
);

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 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)
);

Now we need some data for our database.