homepage Welcome to WebmasterWorld Guest from 23.20.220.61
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / WebmasterWorld / Ecommerce
Forum Library, Charter, Moderators: buckworks

Ecommerce Forum

    
ECommerce database
casturi82




msg:3586817
 7:12 am on Feb 28, 2008 (gmt 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.

thanks

 

ytswy




msg:3586956
 11:30 am on Feb 28, 2008 (gmt 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).

rocknbil




msg:3587190
 3:57 pm on Feb 28, 2008 (gmt 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:


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


options
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:


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


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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / WebmasterWorld / Ecommerce
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved