Re: Storing dynamic attribute data in a db

Chris W. Parker wrote:

Hello, This is now my 3rd attempt at writing this email. :) The first two were pretty long... I'm currently working on trying to find a solution that is both simple and flexible for storing the data of a complicated set of dynamic options for some of our products. My current thinking is that I will use Modified Preorder Tree Traversal to organize the data. Each record will have the following: id (auto-number) sku (related product's sku) lft (hierarchy data) rgt (hierarchy data) attribute (like: Size, Color, Style) option (like: Blue, Large, Plain) pricemodifier (-$20, +$20) This kind of data is not difficult to handle if every combination that is available through the different options is actually available from the manufacturer. However, some combinations are not possible so the data needs to represent itself that way. For example, all t-shirts come in Red, Green, or Blue but only Green shirts come in Large. All other colors have only Small and Medium. Is there a standard way to handle this kind of thing if not, how would you handle it?

I don't think there's a standard way, just whatever works best at the time and most importantly what you understand. If you have to write a 6 page document to explain what's going on, that's probably bad.. because in 6 months time if you need to revisit it, you're going to have issues. Why do you think you need to use a tree? I'm sure it's just a case of me not understanding something.. Anyway I'd move the attributes to another table (pseudo-sql): create table attributes ( attributeid auto increment, attributename ('color'), attributevalue ('blue'), productid references products(id) ); No idea what price modifier is or if it applies to specific attributes but if it does, move it as well. Then you can get all attributes easily: select * from attributes where productid='X'; -- Postgresql & php tutorials http://www.designmagick.com/