|
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.
|
