Building an E-Commerce Site Part 3: Catalogs and Shopping Carts - Step 1: Database Changes (
Page 8 of 12 )
First off, let's go through the database changes since part 2. Fire up MySQL and login as the root user by issuing this command from the shell prompt:
$ mysql -u root -p
You should see MySQL started:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 412 to server version: 3.22.30
Type 'help' for help.
mysql>
Now select the mymarket database:
USE mymarket;
Order Tracking Tables
First thing we need to add is some tables to track customer orders. Specifically, we would like to track the orders that are going through MyMarket, and the products that are being purchased.
We will need two new tables, an orders table and an order_items table. There is a one-to-many relation between orders and order_items, we say that an order can have one or more order items, and order items can belong to one or more orders.
First the orders table to hold the order details. Each record in this table will contain:
- id - An order ID (which is auto-generated by MySQL)
- username - The username of the customer who initiated the order
- o_timestamp - The date/time when the order was created
- a_timestamp - The date/time when the payment was authorized or declined
- status - A status of the authorization process
- status_details - A text message describing the status of the autorization process
- custinfo - Customer information
- comments - Any special instructions or comments to go with the order
- amount - The amount the customer was billed
- An order ID (which is auto-generated by MySQL) - The username of the customer who initiated the order - The date/time when the order was created - The date/time when the payment was authorized or declined - A status of the authorization process - A text message describing the status of the autorization process - Customer information - Any special instructions or comments to go with the order - The amount the customer was billed
Issue this statement to create the table:
CREATE TABLE orders (
id int not null auto_increment,
username varchar(16) not null,
o_timestamp datetime,
a_timestamp datetime,
status tinyint,
status_details varchar(255),
custinfo text,
comments text,
amount float(5,2),
PRIMARY KEY (id)
INDEX username (username)
)
Next, the
order_items table to hold the items that are a part of an order. The fields in this table are:
- order_id - The order ID to which this item belongs
- product_id - The item that was ordered (from the products table)
- price - The price of the item at the time of purchase (this may differ from the price now)
- qty - The and the quantity ordered
- The order ID to which this item belongs - The item that was ordered (from the products table) - The price of the item at the time of purchase (this may differ from the price now) - The and the quantity ordered
Issue this statement to create the table:
CREATE TABLE order_items (
order_id int not null,
product_id int not null,
price float(5, 2) not null,
qty int not null,
PRIMARY KEY (order_id, product_id)
)
On-Special Products
While we're doing database changes, let's also add an extra flag to our products table so we can mark a product as being on-special. We will display on-special products on the MyMarket homepage.
First let's take a look at the products table as it is now:
mysql> DESCRIBE products;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(25) | | MUL | | |
| description | varchar(255) | | | | |
| price | float(5,2) | | | 0.00 | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
We are going to add an extra field called
on_special to flag a product as being on-special. To modify an existing table, we will need to issue an
ALTER statement. Issue this statement like so:
ALTER TABLE products ADD on_special tinyint default 0 AFTER price;
This will add the
on_special field of type
tinyint, the default value be 0. This field will be added after the price field. Now let's look at the table again to verify the new field is there:
mysql> DESCRIBE products;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(25) | | MUL | | |
| description | varchar(255) | | | | |
| price | float(5,2) | | | 0.00 | |
| on_special | tinyint(4) | | | 0 | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Okay, that looks good. We will need to modify our product management scripts to deal with this new
on_special field.
/ 47 
