Products and options are the best way to develop a database.

Question

Description

The store may have goods (shoes, t-shirts, etc.). Each product can have many options, where everyone can have a different price and stock, for example. T-shirt has different colors and sizes.

• Color: blue, size: L, price: 10 US dollars, in stock: 5
• Color: blue, size: XL, price: 10 US dollars, in stock: 10
• Color: White, Size: L, Price: $ 10, In Stock: 6

My solution for this problem is shown in the figure.

http://dl.dropbox.com/u/43925242/erd_product_variant.png

But this solution has some disadvantages.

• it’s difficult to control every version of the same product, it must have the same number of file_ options,
• Finding products through the options features may take some time,
• any other that I don't know about ...?

What can you suggest simplifying the database design for this problem?

===== UPDATE ======

What about a NoSQL solution? Any suggestions?

Answer 1

The main question: "How to store heterogeneous subtypes of the parent class?". There are several options - the one you have chosen is a bit hybrid, which is not necessarily a bad thing.

The best description I've seen in this thread is in Craig Larman's book, Using UML and Templates, although he writes from an object-oriented, not database, perspective.

First of all: the way you set up the “options” may not be the way you want — it means that the “price” and “stock” are moving together, while they are separate separate pieces of data. I would think about expanding them into my own tables - "variant_price" and "variant_stock".

Secondly, the option you have chosen to represent functions is usually called "Object Attribute Value" or EAV. He got a big advantage by allowing him to store data without knowing his schema during development, but makes any boolean queries a huge pain - imagine that you are looking for all red XL shirts.

There are 3 alternatives in the relational world (this is based on Larman's book):

• subtype for each option. Thus, you create the table "variant_tshirt" with size, color, etc. and "variant_trouser" with size, color, inside the leg, etc. This keeps the tables beautiful and self-describing, but makes your SQL a huge mess - it has to change for each subtype.

• separate table with all possible columns: in this case you have one table with all possible fields for all subtypes. Here, your SQL remains much simpler - but the table becomes a huge mess, and you depend on your client application to “know” that trousers have legs inside the attribute, and t-shirts do not.

• a common attribute table with subtypes that store their unique values ​​in their own tables. In this model, if you only have trousers and T-shirts, you have a table of “options” with size and color, and also a table of “pants” with inside legs.

Each option has advantages and disadvantages - especially in a situation where you do not know in advance which subtypes you will need, the first option is the easiest at the end of the database, but it creates a bit of a mess for the client code.

Outside of SQL, you can use XML - using XPath, you can easily execute logical queries or NoSQL, but NoSQL will not be my favorite here, most of them are conceptually based on key value relationships that make logical queries quite difficult.

Answer 2

Rename your products into product categories and options; remove stock (and price, unless most of your options have the same base price) from the categories. Thus, you have several products in the same category. There is currently an ambiguity between Products and Options.

Product features that need to be tested at the application level, and not in the database. You can associate a FeatureList table (containing FeatureName, Required / Optional, and CategoryID) with a category to simplify the process of checking product properties and creating a search template. Or, if most of your categories have a similar set of functions, you can create an NN association with a list of functions; such a structure is a little more difficult to maintain, but much more flexible.

By the way, “hard to control” is not an argument. If there are strict data validation rules, you need to follow them; if they are not, then this is not your task. Implementing a huge list of strict rules is easier than trying to invent what two or three rules a user might want :)

Answer 3

I would suggest several tables, for example, a T-shirt, jeans, etc. etc., i.e. T-shirt A, T-shirt B, then an options table with details such as ProductID (links to the product table), color, size, price, inStockQty.

I would also have a ProductsInCategories table with only CategoryID, ProductID, so that you can display the product in order to appear in several categories at once (think Unisex Tbirts, which you can display both men's t-shirts and women's t-shirts)

You must have a Many-to-Many relationship between a category and a product, using the ProductsInCategories table and a one-to-many relationship between a product and an option (one product can have many options, but an option can only belong to one product)

The flaws you talked about can be overcome with well-designed stored procedures and tables, indexing will also help. In addition, with this tabular structure, it should be easy enough to manage each option, such as updating prices, etc.