Modeling products with vastly different sets of needed-to-know information and linking them to lineitems?

Question

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.

Answer 1

Take a look at this discussion.

Answer 2

Assumptions:

You have some specific products you're selling. I.e., you know you're selling crayons, but not spatulas. The customer doesn't come to your site and try to order a product you've never heard of.

The products you're selling have a pre-existing set of attributes. I.e., crayons have color; crayon_boxes have width, height, crayons... The customer doesn't come to your site and try to specify the value for an attribute you've never heard of.

One way to do this (if you're a RBDM purist, please close your eyes now until I tell you to open them again) is to use an attribute string. So the table would be like this:

Products

+ ProductName
+ ProductAttribute

And then a sample record would be like this:

• Product Name = "Crayon Box"
• Product Attribute = "Height:5 inches;Width:7 inches"

With something like this, parse the name/value pairs in or out as necessary.

Answer 3

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.