用不同的需要知道的信息来建模产品，并将它们链接到lineitems？ [英] Modeling products with vastly different sets of needed-to-know information and linking them to lineitems?

问题描述

我目前正在一个网站上销售不同类型的产品，这些产品是定制的。我有你的一般的，标准的购物车架构：订单有很多LineItems，LineItems有一个产品，但我遇到了一个棘手点：

说我们的产品之一是一个球，我们的一个产品是一盒蜡笔。当人们创建他们的订单，我们最终创建的项目可以由一些伪代码表示：

 球：
属性：
直径：十进制
 color：foreign_ref_to Colors.id 
 material：foreign_ref to Materials.id 
 CrayonBox：
 attributes：
 width：decimal 
 height：decimal 
 front_text：string 
 crayons：many_to_many with Crayon 
 ... 
  

现在，这些都是在创建订单之前创建并存储在我们的db中。我可以很容易地做到这样，当一个项目被添加到购物车，我们得到一个产品名称和价格通过从球或 CrayonBox 在我的控制器并生成 LineItem ，但是如果我们可以为每个行项目提供一整套信息是很好的。 p>

我想过几个可能的解决方案，但没有一个是理想的：

一个：使用中介产品信息链接表，并代表不同的产品，因此我们会有这样的：

  LineItem 
信息：many_to_many with product_information 
 ... 
 ProductInformation：
 lineitem：many_to_many with line_item 
 name：string 
 value：string 
 $ b b ProductInformation（name ='color'，value = $ SOMECOLOR）
 ProductInformation（name ='color'，value = $ SOMEOTHERCOLOR）
 ... 
  / pre> 
 
 
这样做的问题是，需要为产品的每个属性表示的数据的类型并不都属于相同列类型。我可以代表所有的字符串，但$ DEITY知道我甚至不接近认为这是一个很好的解决方案。
 
 
 
我想到的另一个解决方案是让 LineItem 表具有表示产品类型的每个表的外键。不幸的是，这意味着我必须检查在控制器中每个外键的存在。我不太喜欢这一点，但我喜欢它稍微比填充每一块数据到一个数据类型，然后处理所有的转换东西外面的DB。 
 
 
 
另一种可能的解决方案是将产品数据的 tablename 存储在列中，可能是一件好事，可以吗？我失去了数据库链接的东西在一起的能力，它打击了我像类似于使用 eval（）在它不需要 - 我们都知道 eval（）不是真的需要非常频繁。 
 
 
 
我想能够说给我订单项，然后是该订单项的扩展信息，并且有各种产品的正确的信息集类型。 
 
 
 
因此，实际上知道他们在使用数据库模式做什么的人，我该怎么办？我应该如何表示？这似乎是一个相当普遍的用例，但我没有能够找到很多信息与谷歌 - 这是这样的事情有一个共同的模式吗？需要更多信息？这不可能超出你可以使用RDBMS为这个的领域，可以吗？
 
 
 
编辑：我现在相当肯定，希望这里是类表继承。在我的单个模型中使用别名来标准化每个产品类型的信息表后面的链接。不幸的是，ORM我有点卡住使用为此（Doctrine 1.2）不支持类表继承。我可能能够完成类似于Doctrine的列聚合继承，但egh。任何人都认为我咆哮着走错了树？我看过EAV，我不认为它非常适合这个问题 - 关于不同产品的每一组信息是已知的，虽然它们可能从产品类型A到产品类型B非常不同。EAV的灵活性可能很好，但似乎像这样的问题滥用db。
解决方案
这让我感到这是一个完美的契合对于 CouchDB  /  MongoDB ，它允许每个行包含不同的属性，但允许索引查找。使用MySQL建立一个刚性关系部分的混合结构应该是相当简单的，而对于不同形状的部分使用'nosql'。
 
I'm currently working on a site that sells products of varying types that are custom manufactured. I've got your general, standard cart schema: Order has many LineItems, LineItems have one Product, but I've run into a bit of a sticking point:

Lets say one of our products is a ball, and one of our products is a box of crayons. While people are creating their order, we end up creating items that could be represented by some psuedocode:
Ball:
  attributes:
     diameter: decimal
     color: foreign_ref_to Colors.id
     material: foreign_ref to Materials.id
CrayonBox:
  attributes:
     width: decimal
     height: decimal
     front_text: string
     crayons: many_to_many with Crayon
...
Now, these are created and stored in our db before an order is made. I can pretty easily make it so that when an item is added to a cart, we get a product name and price by doing the linking from Ball or CrayonBox in my controller and generating the LineItem, but it would be nice if we could provide a full set of info for every line item.

I've thought of a few possible solutions, but none that seem ideal:

One: use an intermediary "product info" linking table, and represent different products in terms of that, so we'd have something like:
LineItem
  information: many_to_many with product_information
  ...
ProductInformation:
  lineitem: many_to_many with line_item
  name: string
  value: string

ProductInformation(name='color', value=$SOMECOLOR)
ProductInformation(name='color', value=$SOMEOTHERCOLOR)
...
The problem with this is that the types of data needed to be represented for each attribute of a product does not all fall under the same column type. I could represent everything with strings, but $DEITY knows I don't even come close to thinking that's a good solution.

The other solution I've thought of is having the LineItem table have a foreign key to each table that represents a Product type. Unfortunately, this means I would have to check for the existence of each foreign key in my controller. I don't like this very much at all, but I like it marginally better than stuffing every piece of data into one datatype and then dealing with all the conversion stuff outside of the DB. 

One other possible solution would be to store the tablename of the product data in a column, but that can't possibly be a good thing to do, can it? I lose the capability of the db to link stuff together, and it strikes me as akin to using eval() where it's not needed -- and we all know that eval() isn't really needed very often. 

I want to be able to say "give me the line item, and then the extended info for that line item", and have the correct set of information for various product types. 

So, people who actually know what they're doing with database schema, what should I be doing? How should I be representing this? This seems like it would be a fairly common use case, but I haven't been able to find much info with googling -- is there a common pattern for things like this? Need more info? This can't possibly be outside of the realm of "you can use a RDBMS for this", can it?

Edit: I'm now fairly certain that what I want here is Class Table Inheritance. with an alias in my individual models to "normalize" the link followed to the "info" table for each product type. Unfortunately, the ORM I'm kinda stuck using for this (Doctrine 1.2) doesn't support Class Table Inheritance. I may be able to accomplish something similar with Doctrine's "column aggregation" inheritance, but egh. Anyone think I'm barking way up the wrong tree? I looked over EAV, and I don't think it quite fits the problem -- each set of information about different products is known, although they might be very different from product type A to product type B. The flexibility of EAV may be nice, but it seems like an abuse of the db for a problem like this.
解决方案
It strikes me that this is a perfect fit for the likes of CouchDB / MongoDB which allow every 'row' to contain different attributes, yet permits indexed lookups. It should be fairly straightforward to build a hybrid structure using MySQL for the rigid relational parts and 'nosql' for the parts of varying shape.

                        
这篇关于用不同的需要知道的信息来建模产品，并将它们链接到lineitems？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！