Search code examples
mysqlsqldatabase-designentity-attribute-value

DB Design to allow user to define products, product specs and let themself insert orders

I'm designing a db because I need to develop such a CRM, where the users are able to specify new products, products specs and pricing and then let themself to insert orders for that products specified in a previous step. Of course the data inserted needs to be evaluated according to what they specified in the db.

I may be more clear with an example: user1 creates a product "apple", specifies then "color" and "weight" as attribute specs for that product. Then user1 says that the apple pricing is based on the color and the weight. The apple "yellow" has a price of 1$/weight unit, the "red" apple has a price of 2$/weight unit.

It also creates a product "car" with specs "year", "model", "color", "brand". And also here it specifies prices combinations. Finally he creates the product "shirts" with spec "size" but where the price is standard for each size

What's a good starting point to handle this kind of data? I was thinking to create a table products (id, name, pricing_type) (pricing_type gives an idea if it's a fixed pricing or a variable pricing according to some field). Then productSpecsName (id, relatedProduct FK products (id), name) and productSpecsValue (id, relatedSpecsName FK productSpecsName(id), value, conversion) (conversion tell me how to use that data in the code -such as transform it as integer, float or boolean, because value is always a string).

But this design doesn't give me as much flexibility as I need, can you help me getting a better design? It's such an ecommerce but I would like to give the user the opportunity to create custom types. The first, and probably simplest design I came with is to create a table per product (each time the user needs to add a product I create a new table) or create a mega-table with all possible options and leave them nullable, but I would go with a more flexible option. Waiting for better ideas/suggestions.

Solution

  • I think you are looking for Entity–attribute–value model.See this pages as a start:

    You can also find useful threads here in SO in the tag: