Shopping Cart Database Design

The database design for our shopping cart is quite simple. Below is the summary of what tables we need for
this shopping cart plus the short description of each table. You can see the complete SQL needed to build the database here

Table Name		Description
tbl_category		Storing all product categories
tbl_product		The products ( what else )
tbl_cart		When the shopper decided to put an item into the shopping cart we’ll add the item here
tbl_order		This is where we save all orders
tbl_order_item		The items ordered
tbl_user		Store all shop admin user account
tbl_shop_config		Contain the shop configuration like name, address, phone number, email, etc

The ER ( Entity Relationship ) diagram is shown below.

Now, let’s take a better look at each table

tbl_category

This table store the product categories. From the ER diagram you can see that our current database design enables a category to have a child category and for the child category to have another child category and so on. But for this tutorial we make a restriction that the category will only two level deep like this “Top Category > Manga > Naruto”. The reason is to reduce the number of clicks required by a visitor when browsing a category.
Another rule is that a product can only be added on the second level category. For example if we have this category structure :
Top Category > Manga > Naruto
then we can only add a product in “Naruto”, not in “Manga”. The top level categories will not contain any products and a product can only belong to one category.

tbl_product

In this table we store the product’s name, category id, description, image and thumbnail. For now a product can only have one image. It may not be enough if you want to show a picture of you product from multiple angles so i plan to improve this on future version.
When adding a product image in the admin page we don’t need to upload the thumbnail too. The script will generate the thumbnail from the main image. The thumbnail size is defined in library/config.php ( THUMBNAIL_WIDTH ) and currently it is set to 75 pixels.

tbl_cart

This table will store all items currently put by the customer. Here we have ct_session_id to save the id of a shopping session. We will explore this further when adding a product to shopping cart

tbl_order

Finally when the customer finally place the order, we add the new order in this table. The shipping and payment information that the customer provided during checkout are alos saved in this table including the shipping cost.
For the order id i decided to use an auto increment number starting from 1001.
Why start at 1001 ?
Because an order id looks ugly ( at least for me ^^ ) if it’ s too short like 1, 2 or 3 so starting the order id from 1001 seems to be a good idea for me.
To make the order id start from 1001 we use the following sql :

CREATE TABLE tbl_order (
   id int(10) unsigned NOT NULL auto_increment,
   date datetime default NULL,
   last_update datetime NOT NULL default ’0000-00-00 00:00:00′,
   status enum(‘New’, ‘Paid’, ‘Shipped’,'Completed’,'Cancelled’) NOT NULL default ‘New’,
   memo varchar(255) NOT NULL default ”,
   shipping_first_name varchar(50) NOT NULL default ”,
   shipping_last_name varchar(50) NOT NULL default ”,
   shipping_address1 varchar(100) NOT NULL default ”,
   shipping_address2 varchar(100) NOT NULL default ”,
   shipping_phone varchar(32) NOT NULL default ”,
   shipping_city varchar(100) NOT NULL default ”,
   shipping_state varchar(32) NOT NULL default ”,
   shipping_postal_code varchar(10) NOT NULL default ”,
   shipping_cost decimal(5,2) default ’0.00′,
   payment_first_name varchar(50) NOT NULL default ”,
   payment_last_name varchar(50) NOT NULL default ”,
   payment_address1 varchar(100) NOT NULL default ”,
   payment_address2 varchar(100) NOT NULL default ”,
   payment_phone varchar(32) NOT NULL default ”,
   payment_city varchar(100) NOT NULL default ”,
   payment_state varchar(32) NOT NULL default ”,
   payment_postal_code varchar(10) NOT NULL default ”,
   PRIMARY KEY (   id)
) TYPE=MyISAM AUTO_INCREMENT=1001 ;

You see, we just need to add AUTO_INCREMENT = 1001 right after the create definition.

tbl_order_item

All ordered items are put here. We simply copy the items from the cart table when the customer place the order.

tbl_shop_config

This table store the shop information. For now it only have the shop name, address, phone number, contact email address, shipping cost, the currency used in the shop and a flag whether we want to receive an email whenever a customer place an order.

tbl_user

This table save all the user or admin account. Currently all user is an admin and all can do everything to the shop. I’m planning to add permission level so one admin can do everything, while the other user can only add / update product, manage orders, etc.
By the way, we will be using indexes on the tables to speed up queries. As a matter of fact whatever application you make using indexes is a good idea because it can improve the database query performance.
Okay, next we talk about the database abstraction. It’s not a difficult stuff so you can skim read it if you like.