问题描述
我正在设计一个数据库，因为我需要开发这样的CRM，用户可以在其中指定新产品，产品规格和定价，然后让自己为上一步中指定的产品插入订单。当然，插入的数据需要根据db中指定的数据进行评估。
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.
我可能会更清楚一个例子：user1创建一个产品apple指定颜色和重量作为该产品的属性规格。那么user1表示苹果定价是基于颜色和重量。苹果黄色的价格为1美元/重量单位，红色苹果的价格为2美元/重量单位。
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
这是一个很好的起点处理这种数据的？
我正在考虑创建一个表产品（id，name，pricing_type）（pricing_type给出一个想法，如果它是固定价格或根据某些领域的可变价格）。
然后 productSpecsName（id，relatedProduct FK products（id），name）和 productSpecsValue（id，relatedSpecsName FK productSpecsName（id） ，转换）（转换告诉我如何在代码中使用该数据，例如将其转换为integer，float或boolean，因为值始终为字符串）。
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.
推荐答案
我想你在寻找。看到这个页面作为开始：
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: eav
这篇关于DB设计允许用户定义产品，产品规格并让自己插入订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！