Creating the Database

The e-commerce site in this example will use the simply named ecommerce database. It was marginally used in Chapter 11, but without the structure it will have here. I'll explain each table's role prior to creating the database in MySQL.

With any type of e-commerce application there are three broad kinds of data to be stored: the product information (what is being sold), the customer information (who is making purchases), and the order information (what was purchased and by whom). Going through the normalization process (see Chapter 5, "Advanced SQL and MySQL"), I've come up with five tables (Figure 14.1).

Figure 14.1. This entity-relationship diagram (ERD) shows how the five tables in the ecommerce database relate to one another.

The first two tables store all of the products being sold. As I said before, the site will be selling artistic prints. The artists table (Table 14.1) stores the information for the artists whose work is being sold. This table contains just a minimum of information (the artists' first, middle, and last names), but you could easily add the artists' birth and death dates, biographical data, and so forth. The prints table (Table 14.2) is the main products table for the site. It stores the print names, prices, and other relevant details. It is linked to the artists table using the artist_id.

Table 14.1. The artists table will be used to link artist names to each individual print (see Table 14.2).

The artists Table
COLUMN	TYPE
artist_id	INT(3) UNSIGNED NOT NULL
first_name	VARCHAR(20) default NULL
middle_name	VARCHAR(20) default NULL
last_name	VARCHAR(30) NOT NULL

Table 14.2. The prints table is the equivalent of a products table in other e-commerce applications. Items listed in the prints table will be purchased by the customer.

The prints Table
COLUMN	TYPE
print_id	INT(4) UNSIGNED NOT NULL
artist_id	INT(3) UNSIGNED NOT NULL
print_name	VARCHAR(60) NOT NULL
price	DECIMAL(6,2) NOT NULL
size	VARCHAR(60) default NULL
description	VARCHAR(255) default NULL
image_name	VARCHAR(30) NOT NULL

The customers table (Table 14.3) does exactly what you'd expect: it records the personal information for each client. At the least, it reflects the person's first name, last name, email address, password, and shipping address, as well as the date they registered. Presumably the combination of the email address and password would allow the user to log in, shop, and access their account. Since it's fairly obvious what information this table would store, I'll define it with only the three essential columns for now.

Table 14.3. The customers table is being defined in the most minimal way for the purposes of this chapter's example. Expand its definition to suit your application's needs

The customers Table
COLUMN	TYPE
customer_id	INT(5) UNSIGNED NOT NULL
email	VARCHAR(40) NOT NULL
pass	CHAR(40) NOT NULL

The final two tables store all of the order information. There are any number of ways you could do this, but I've chosen to store general order informationthe total, the date, and the customer's IDin an orders table (Table 14.4). This table could also have separate columns reflecting the shipping cost, the amount of sales tax, any discounts that applied, and so on. The order_contents table (Table 14.5) will store the actual items that were sold, including the quantity and price. The order_contents table is essentially a middleman, used to avoid the many-to-many relationship between prints and orders (each print can be in multiple orders, and each order can have multiple prints).

Table 14.4. The orders table will record the customer's ID, the order total, and the date of the order.

The orders Table
COLUMN	TYPE
order_id	INT(10) UNSIGNED NOT NULL
customer_id	INT(5) UNSIGNED NOT NULL
total	DECIMAL(10,2) NOT NULL
order_date	TIMESTAMP NOT NULL

Table 14.5. The order_contents table stores the specific items in an order.

The order_contents Table
COLUMN	TYPE
oc_id	INT(10) UNSIGNED NOT NULL
order_id	INT(10) UNSIGNED NOT NULL
print_id	INT(4) UNSIGNED NOT NULL
quantity	TINYINT UNSIGNED NOT NULL DEFAULT 1
price	DECIMAL(6,2) NOT NULL
ship_date	DATETIME DEFAULT NULL

In order to be able to use transactions (see Chapter 11), the two order tables will use the InnoDB storage engine. The others will use the default MyISAM type. See Chapter 5 for more information on the available storage engines (table types).

To create the database

1.	Log in to the mysql client and create the ecommerce database, if it doesn't already exist. CREATE DATABASE ecommerce; USE ecommerce; For these steps, you can use either the mysql client or another tool like phpMyAdmin.
2.	Create the artists table (Figure 14.2). CREATE TABLE artists ( artist_id INT(3) UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(20) default NULL, middle_name VARCHAR(20) default NULL, last_name VARCHAR(30) NOT NULL, PRIMARY KEY (artist_id), KEY full_name (last_name, first_name) ) ENGINE=MyISAM; Figure 14.2. Making the first table. This table stores just four pieces of information for each artist. Of these, only last_name is required (is defined as NOT NULL), as there are single-named artists (e.g., Christo). I've added definitions for the indexes (or keys) as well. The primary key is the artist_id, and an index is placed on the combination of the first and last name, which may be used in an ORDER BY clause.
3.	Create the prints table (Figure 14.3). CREATE TABLE prints ( print_id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT, artist_id INT(3) UNSIGNED NOT NULL, print_name VARCHAR(60) NOT NULL, price decimal(6,2) NOT NULL, size VARCHAR(60) default NULL, description VARCHAR(255) default NULL, image_name VARCHAR(30) NOT NULL, PRIMARY KEY (print_id), KEY artist_id (artist_id), KEY print_name (print_name), KEY price (price) ) ENGINE=MyISAM; Figure 14.3. Making the second table. All of the columns in the prints table are required except for the size and description. I've also set indexes on the artist_id, print_name, and price fields, each of which may be used in queries. You could add to this table an in_stock field, to indicate the availability of products.
4.	Create the customers table (Figure 14.4). CREATE TABLE customers ( customer_id INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(40) NOT NULL, pass CHAR(40) NOT NULL, PRIMARY KEY (customer_id), KEY email_pass (email, pass) ) ENGINE=MyISAM; Figure 14.4. Creating a basic version of the customers table. This is the code used to create the customers table. You could throw in the other appropriate fields (name, address, phone number, the registration date, etc.). As I won't be dealing with those valuesor user management at allin this chapter, I've omitted them.
5.	Create the orders table (Figure 14.5). CREATE TABLE orders ( order_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT(5) UNSIGNED NOT NULL, total decimal(10,2) NOT NULL, order_date TIMESTAMP, PRIMARY KEY (order_id), KEY customer_id (customer_id), KEY order_date (order_date) ) ENGINE=InnoDB; Figure 14.5. Making the orders table. All of the orders fields are required, and three indexes have been created. Notice that a foreign key column here, like customer_id, is of the same exact type as its corresponding primary key (customer_id in the customers table). The order_date field will store the date and time an order was entered. Being defined as a TIMESTAMP, it will automatically be given the current value when a record is inserted. Finally, because I'll want to use transactions with the orders and order_contents tables, both will use the InnoDB storage engine.
6.	Create the order_contents table (Figure 14.6). CREATE TABLE order_contents ( oc_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, order_id INT(10) UNSIGNED NOT NULL, print_id INT(4) UNSIGNED NOT NULL, quantity TINYINT UNSIGNED NOT NULL DEFAULT 1, price decimal(6,2) NOT NULL, ship_date DATETIME default NULL, PRIMARY KEY (oc_id), KEY order_id (order_id), KEY print_id (print_id), KEY ship_date (ship_date) ) ENGINE=InnoDB; Figure 14.6. Making the final table for the ecommerce database.

In order to have a normalized database structure, I've separated out each order into its general informationthe customer, the order date, and the total amountand its specific informationthe actual items ordered and in what quantity. The table has foreign keys to the orders and prints tables. The quantity has a set default value of 1. The ship_date is defined as a DATETIME, so that it can have a NULL value, meaning that the item has not yet shipped. Again, this table must use the InnoDB storage engine in order to be part of a transaction.

You may be curious why I'm storing the price in this table when that information is already present in the prints table. The reason is simply this: the price of a product may change. The prints table indicates the current price of an item; the order_contents table indicates the price at which an item was purchased.

Tips

• Depending upon what a site is selling, it would have different tables in place of artists and prints. The most important attribute of any e-commerce database is that there is a products table that lists the individual items being sold with a product ID associated with each. So a large, red polo shirt would have one ID, which is different than a large, blue polo shirt's ID, which is different than a medium, blue polo shirt's ID. Unique, individual product identifiers let you track orders and product quantities.

• If you wanted to store multiple addresses for usershome, billing, friends, etc.create a separate addresses table. In this table store all of that information, including the address type, and link those records back to the customers table using the customer ID as a primary-foreign key.