Let me set up a scenario for this: There's a products table, and there's a product type table. The product table has a product per row with a field with the id of a product type row, so each product has a type defined. I want some types to have extra fields, not present in the product table. For instance, products of type 'container' might have the field 'capacity'. I want the extended field information to be tied to the product type, but the actual extended field data to be tied to each individual product. What's a good way to impliment this.
I've got some thoughts I'll throw out.
The extended field data could be stored either as a CSV style list in a text field in the article table, or it could be stored in another table with the fields product id, field name, field value. Which do you think would be more efficient? The CSV style requires a CSV transform when you read or write, but the other requires an additional query on read and a bunch of additional queries on write.
The extended information fields for the type table could either be stored in CSV fasion (really just requiring a simple implode/explode since it's field names), or in another table with fields product type id, field name. The second option allows more information, like data type and such to be stored for the fields, which would be very useful for creating HTML inputs for those fields.
Any comments, sudgestions?
Thanks, Matt
