Representing Ecommerce Products and Variations Cleanly in the Database

Best database structure for ecommerce website

Umm.. i came up with this structure, hope that will help a bit. One should have a Master Table with the product information. Here in ur case, its product id, size and color repec. and rest of the tables connects to the master table

1.table products_id (master table)
     1.id- Primary Key
     2.id_product_size- References size.id (FK)
     3.id_product_color- References color.id (FK)

 2. table product_name
     1.product_id- References product_id.id(FK)
     2.name
     3.parents

 3.table size 
     1.id- Primary Key
     2.size_name
     3.quantity

 4.table color 
     1.id- Primary Key
     2.colors
     3.quantity

How to model products in an online store application

I have seen two solutions to this kind of dilemma. The first is to try to use characteristics to define subordinate products to the "main" product. The challenge here is that in addition to your thoughts for far, in most cases the product will evolve with new manufacturers that bring new aspects to the table. For example, one manufacturer may make a cheaper product, but have a different application method for the logo or stitching that may be significant enough to track.

I think that carrying a non significant product number for each product and then attaching the characteristics as attributes works out the best. It is easily searched and extensible. If a group of products are strongly related, a ProductGroup that the individual products attach to works well.

In tables:

            ProductGroup
            --------------------
            ProductGroupID
            ProductGroupName
            ProductGroupDescription

            Product
            --------------------
            ProductID
            ProductGroupID
            QtyOnHand
            BasePrice
            ProductColorID
            ProductSizeID

            ProductColor
            ------------
            ProductColorID
            ProductColorName

            ProductSize
            --------------
            ProductSizeID
            ProductSizeName

            ...more attributes...

The advantages here are that you can easily query for specific attributes, attributes are "flexible" in that more can be added (and old ones adjusted: if you started with "Red" but then added another "Red" to the color pool, you can change them to "Maroon" and "Bright Red".

You can control price and inventory are at the detail product level (although more tables may be required to account for sourcing costs).

This all assumes that your characteristics are universally shared. If they are not, your characteristic subtable approach can work by creating a join table between characteristics and the product detail tables and populate as needed. This will require more business logic .to ensure each product category gets all characteristics necessary. In this latter case I would use "prototype" products in the base product table (with Qty and Cost of 0) that I would clone the characteristics from and then adjust as each new product is entered. As you move forward, when a new variation appears, having a "clone this product" function that allows you to just adjust the differences from the base product would be valuable.

Finally, as far as managing the inventory and pricing, this is going to happen at the UI layer. Being able to generate queries for related products (product groups) and manage all the pricing for related products will go a long way to making this livable.

Related Topics