Completed Database Desgin

After Normalization done in 3 steps, I use Entity Relationship Diagram (ERD) to represnt relationships among tables.

What is ERD ?
It is a specialized graphic that illustrates the interrelationships between entities in a database

tbl_category

This table store the product categories. From the ER diagram we can see that our current database design enables a category to have a child category and a master category.

For example:

Top category

=> Cooking

• Sauces


• Cooking Ingredients

=> Noodles

• Instant Noodle


• Noodle Sticks

• 
  

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 > Noodle (level 1) >> Instant Noodle (level 2)

then we can only add a product in "Instant Noodle", not in "Noodle". The top level categories will not contain any products and a product can only belong to one category.

tbl_product

In this table I store the product's name, category id, description, image and thumbnail. For now a product can only have one image.

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. Each time, customer click on the 'Add To Cart' button on the product detail page he/she will be redirected to the shopping cart page which view all item he/she selected.

tbl_order

next, 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 also saved in this table including the shipping cost.

tbl_order_item

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

tbl_shop_config

This table stores 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 whether we want to receive an email whenever a customer place an order.

tbl_user

This table saves relevant data into admin account. Currently an admin can do everything to the shopsuch as add / update product, manage orders, etc.

tbl_customer

This table saves relevant data into customer account. Currently a customer can register a new acount, login, edit password and their shipping and payment information (name, address, etc).