Welcome to WebmasterWorld Guest from

Forum Moderators: buckworks

Message Too Old, No Replies

ECommerce database

7:12 am on Feb 28, 2008 (gmt 0)

New User

10+ Year Member

joined:Feb 28, 2008
votes: 0

Hey guys,

I am planning to develop an e-store for my project. I am stuck at how the database tables should be designed. I mean, the store would provide different kinds of products like electronics, books, cell phones, etc. I want to know how to come up with tables for all these products as each of them have different attributes and one table cannot depict all products. Should I design one table for each type of product?
I would be glad if I could get some pointers to this.


11:30 am on Feb 28, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:June 25, 2002
votes: 0

I'd recommend having a look at an open source cart - oscommerce for example - and see how they do it. Oscommerce gets a lot of (valid) criticism, but its database structure is relatively sane IMO.

Categories are pretty easy - you have a product table (uid, product name, price, etc.). You have a category table (uid, category name, link to parent (set to null for top level)). You then have a products to categories link table (product id, category id) - this gives you a many to many relationship, so each category can hold multiple products, and each product can be in multiple categories).

Attributes are a bit more complicated, since you need to link products to not only the attribute type (colour, flavour, etc) but also the values allowed for those attributes for that specific product (not all products will be available in all possible colours for eg), and you will want to be able to vary the price on a product-attribute-option basis (so you store this information in one of your link tables). Again, I'd recommend looking at oscommerce - it has a pretty full-featured attribute system.

In general, you do NOT want multiple tables holding the same type of information (eg more than 1 product table), you have 1 table for each entity type and link this in to other tables to give you the data structure you need (although for optimisation purposes you may have multiple tables for some entities joined on a strict 1-1 basis, simply to avoid having to pull large amounts of data on, for example, a product, every time you just want its price).

3:57 pm on Feb 28, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

Relational tables will work for you. The stickiest area for building a product database lies in the product options. If you construct it right, your products can have an unlimited number of options, and those options can be "anything". They can also add to the cost of the item, as in a cover for your widget, or they can be a required option, such as color. A rudimentary example:

rec_id ¦ prod_id ¦ sku ¦ title ¦ description ¦ price
112 ¦ 2345 ¦ WID01 ¦ Widget ¦ A Widget ¦ 20.00

rec_id ¦ product_id ¦ name ¦ value ¦ required ¦ description ¦ price
3345 ¦ 2345 ¦ Color ¦ Red ¦ 1 ¦ ¦ 0.00
3346 ¦ 2345 ¦ Color ¦ Green ¦ 1 ¦ ¦ 0.00
3347 ¦ 2345 ¦ Color ¦ Blue ¦ 1 ¦ ¦ 0.00
3348 ¦ 2345 ¦ Size ¦ S ¦ 1 ¦ ¦ 0.00
3349 ¦ 2345 ¦ Size ¦ M ¦ 1 ¦ ¦ 0.00
3350 ¦ 2345 ¦ Size ¦ L ¦ 1 ¦ ¦ 0.00

So for this item, your "admin area" creates options named Size and Color for this item. As you can see, you can add unlimited option values (add 100 colors) for that item, or add unlimited options specific to that item. The "required" field is critical in the final checkout so you can make sure if an item is required that it has been selected.

The options table could be optimized even further by not using textual values "Size" and "M", but create yet another table holding these values, and the options table use integer values instead of textual values. Also, this approach is not fully normalized, because you have multiple values for the option name and required fields - these should be moved into another table and joined on selection.

Take the above scenario applied to an item that has two options: one is required and changes the price, and one that is NOT required and adds to the price. Using the widget cover as an example:

rec_id ¦ prod_id ¦ sku ¦ title ¦ description ¦ price
112 ¦ 2345 ¦ WID01 ¦ Widget ¦ A Widget ¦ 0.00

rec_id ¦ product_id ¦ name ¦ value ¦ required ¦ description ¦ price
3345 ¦ 2345 ¦ Size ¦ S ¦ 1 ¦ ¦ 10.00
3346 ¦ 2345 ¦ Size ¦ M ¦ 1 ¦ ¦ 20.00
3347 ¦ 2345 ¦ Size ¦ L ¦ 1 ¦ ¦ 30.00
3348 ¦ 2345 ¦ Cover ¦ X ¦ 0 ¦ Cover ¦ 5.00

So when you calculate the item cost, if there is a value in the option the option price is the price used, not the base product price. Any options that are not required are added to the total.

This concept applies as well to inventory tables, if you use them.