4

I have looked at the other discussions about the subject notably:

Representing ecommerce products and variations cleanly in the database

and:

DB schema help, linking variations of products

But I find them lacking and simplistic.

I am looking for a production quality schema to support products and product variations such as color, size, left/right handed, capacity etc.

Also I need to support product kits, related products, dependent products. Also I need to support product lines/styles, such as all products within a style such faucets and shower head in the "windsor" style.

Any help is appreciated, if you are willing to share something so valuable.

Thanks

Community
  • 1
  • 1
sdfor
  • 6,324
  • 13
  • 51
  • 61

2 Answers2

2

If you intend to place all this into a relational database (your SQL tag makes me think so), you need to plan that well. You should look out for a Database Admin (DBA) who is experienced in the field and can go through your specification en detail together with you.

If you don't have a specification of what you need to do upfront, you might want to look into a document based database like couchdb which will offer you more flexibility for variations within the data and properties of the product entity. The work to manage/normalize the data is then more within your application's code then inside the relational database.

hakre
  • 193,403
  • 52
  • 435
  • 836
  • Thanks you and yes, my intention is to use a SQL relational database. I have been working on this for a while and it's quite complex. I was hoping for some insights. Clearly I'm not the first to tackle this problem. I'm experienced enough to incorporate any suggestions with my requirements. – sdfor Dec 23 '11 at 18:20
  • It probably helps if you look into the database design of existing opensource ecommerce solutions like OSCommerce (has rudimentary variations, so probably a good general starting point into the topic), Magneto and Oxid Eshop for example. Use a datbase tool to visualize the database structure. – hakre Dec 23 '11 at 18:30
  • good idea. I am doing that now. – sdfor Dec 23 '11 at 18:34
2

From my experience what you are looking for (in the sake of simplicity) is a multi-table relational structure. Based on the information you gave I have worked up a draft below

Product Table

Used to maintain your product list

[product]
   - id  (BIGINT, AUTO_INC, PK, NOT NULL)
   - title (VARCHAR(255), INDEX, NOT NULL)

Product Properties Table

Everything unique about your properties that they may or may not have in common (ie. size color, left/right, capacity, etc.)

[product_property]
   - id (INT, AUTO_INC, PK, NOT NULL)
   - title (VARCHAR(255), INDEX, NOT NULL)

Product Property Values Table

This table is where you tell it what product's property has what value (ie. this product 10234-3 is black in color, and has a capacity of 25). I used the BLOB type for value because the question is open eneded. Is this a quantity number, or a three page text description. You can adjust as necissary to fit your needs. You can also use this table to handle dependency it does not need to be unique combination of product_id and property_id because you may have one or more colors, or multiple groups it can belong to, etc.

[product_property_value]
   - product_id  (BIGINT, FK => product.id, NOT NULL )
   - property_id (INT, FK => product_property.id, NOT NULL)
   - value (BLOB)

If you have any questions or need clarification I will help where i can. Its pretty basic and should fit your needs.

PseudoNinja
  • 2,846
  • 1
  • 27
  • 37