Planning and Creating the Database Tables

Because the goal of this chapter is to provide the user with a way to select and order items, you can imagine what the tables will befirst and foremost, you need a table to hold the shopping cart information. In addition to the cart table, you'll need a table to store orders, along with one to store the items purchased as part of each order.

The following SQL statements were used to create the three new tables, starting with the store_shoppertrack table. This is the table used to hold items as users add them to their shopping cart.

mysql> create table store_shoppertrack (
    -> id int not null primary key auto_increment,
    -> session_id varchar (32),
    -> sel_item_id int,
    -> sel_item_qty smallint,
    -> sel_item_size varchar(25),
    -> sel_item_color varchar(25),
    -> date_added datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

In this table, the only key is the id field for the record. The session_id cannot be unique; otherwise, users could order only one item from your store, which is not a good business practice.

The value stored in the session_id field identifies the user; it matches the value of the PHP session ID assigned to them. The sel_* fields hold the selections by the user: the selected item, the selected quantity of the item, and the selected color and size of the item. Finally, there's a date_added field. Many times, users place items in their cart and never go through the checkout process. This practice leaves straggling items in your tracking table, which you may want to clear out periodically. For example, you might want to delete all cart items more than a week oldthis is where the date_added field is helpful.

The next table holds the order information:

mysql> create table store_orders (
    -> id int not null primary key auto_increment,
    -> order_date datetime,
    -> order_name varchar (100),
    -> order_address varchar (255),
    -> order_city varchar (50),
    -> order_state char(2),
    -> order_zip varchar(10),
    -> order_tel varchar(25),
    -> order_email varchar(100),
    -> item_total float(6,2),
    -> shipping_total float(6,2),
    -> authorization varchar (50),
    -> status enum('processed', 'pending')
    -> );
Query OK, 0 rows affected (0.00 sec)

The only key field in the store_orders table is the id. For the sake of brevity in this lesson, an assumption is made that the billing and shipping addresses of the user are the same, and that this store sells only to United States addresses. It's simple enough for you to add another block of fields for shipping address information, if you want to do so.

Also, this table assumes that you are not storing credit card information, which you shouldn't do unless you have super-encrypted the information and are positive your firewalled server is secure. Instead, this table is based on the idea of real-time, credit card processing. You'll learn a few transaction options at the end of this lesson.

The final table is the table to hold the line items in each order,

store_orders_items:

mysql> create table store_orders_items (
    -> id int not null primary key auto_increment,
    -> order_id int,
    -> sel_item_id int,
    -> sel_item_qty smallint,
    -> sel_item_size varchar(25),
    -> sel_item_color varchar(25),
    -> sel_item_price float(6,2)
    -> );
Query OK, 0 rows affected (0.00 sec)

The sel_* fields should look familiarwith the exception of sel_item_price, they are the same fields that appear in the store_shoppertrack table! The primary key is the id field, and the order_id field is used to tie each line item to the appropriate record in store_orders.

The sel_item_price field is included here, as opposed to simply relating to the item record, because you might have occasion to change the pricing in your item record. If you change the price in the item record, and you relate the sold line items to the current catalog price, your line item prices won't reflect what the user actually paid.

With your tables all squared away, we can move on to adding an item to the user's shopping cart.