How should I create this customized e-commerce database model?

Question

I am building an e-commerce website from scratch and have to make a special product configuration page.

It's only 1 type of product, but it is configurable on several levels:

1. Color (about 4 different options). Value is a VARCHAR.
2. Material (about 10 different options). Value is a VARCHAR.
3. Size (About 30 different options). Has 2 Values, a width column and a height column.
4. Finish (About 20 different options). Value is a VARCHAR.
5. Other various VARCHAR options etc.

My question is what would this look like in a typical MySQL database. Do I have a table for each type of option or just one table and somehow give it enough columns and have it store all options? I will need to store orders and be able to store the information for the order in a table as well.

I also want to be able to have off the shelf products that aren't customizeable, just like a normal store.

Any help is appreciated!!

Answer 1

I suggest that you go with one master table, with all of the product information, and a slew of lookup tables, that connects to the master table.

It should look like this:

Product_table:
   ID primary key,
   ColorInt int foreign key lkp_Color_table.ID,
   Material_int foreign key lkp_Material_table.ID,
   // snip the rest

And here's the lookup table:

lkp_color_table:
   ID primary key,
   ColorStr varchar,
   description varchar

lkp_Material_table:
   ID primary key,
   MaterialStr varchar,
   description varchar

The Product_table can look like this:

ID||ColorInt||Material
1 ||1       ||1
2 ||1       ||2

Whereas the lkp_color_table can look like this:

ID||colorstr||description
1 ||red     ||red color
2 ||blue    ||blue color

Note that lkp_color_table can contain unused color , same goes for other lookup tables. So if you have 30 possible colors, you just have to populate lkp_color_table with 30 items, and so on.

There is no need to create a separate Product_id for each color-material- combination, you just have to create a product_id if you use it.

Answer 2

I'd say that you'll need:

OrderTable (OrderID, CustomerID, Date, Price, Shipment, ...)

OrderProductTable (OrderID, ProductID, Quantity)

ProductTable (ProductID, StandardBit, ColorID, Option2ID, ..., OptionNID); StandardBit - to know if product is standard or configurable.

CustomerTable (CustomerID, ...)

ShipmentTable ()

OptionTable for every configurable option (OptionID, Description).

Answer 3

2 + N tables:

• Products (every single product in the catalog has a record here
• SpecialKindOfProduct (Your configurable product type, a product_id, and a bunch foreign keys to lookup tables (color_id, size_id, etc)
• N Lookup tables (colors, sizes, etc)

When you add a second kind of configurable product in the future, you just create AnotherSpecialKindOfProduct.

Hope this is clear enough to follow.

The main advantage is that all your products have some shared attributes (the columns of "Products"), but can have extended attributes as well.

You can easily select everything you need by LEFT JOINing Products to SpecialKindOfProduct, etc.