Modeling product attributes/options

[allspiritseve]

I'm not sure what the actual term is for these, but my boss calls them attributes and I've heard them called options: colors, sizes, etc. that may have varying prices. I currently have a table for products and a table for product attributes, but I'm running into a lot of problems that could be solved by refactoring my passed arrays into objects. (As I did the content pages first, products seemed easy enough using Gateways/arrays, but then when doing the shopping cart, I switched to mappers/objects which made my life a whole lot easier. Now that I'm doing more complex things with products, those arrays are getting mighty troublesome).

I guess I'm wondering how people would model products and their attributes. Currently I only need one attribute per product (typically size) and each attribute has a different price (most of what they sell is wine). Should I consider each attribute (ie, 750ml 'Blanc de Blanc') a "Product", and then each actual Product (ie, 'Blanc de Blanc') moved to some sort of product 'type'? I currently have prices in the attributes table. Also, when I am grabbing information for my cart I am grabbing all product_attribute values, but only the product name. That leads me to think the attribute is really the product, but I am unsure.

Edit: 1. I noticed Magento uses some EAV tables for their attributes. Is this common? I suppose its one of those situations where something like that becomes useful. 2. I have been thinking, and it seems like attributes should be fixed characteristics whereas options are what I'm looking for: things the user has to specify in order to place an order (such as bottle size). Sometimes that involves a changing price (375ml vs. 750ml wine bottle) and sometimes the price is fixed (S vs L shirt). That makes the most sense to me, though I still am not sure how they should be stored.

[Mastodont]

Regarding to EAV, I have recently browsed various discussions and all people (http://www.dbforums.com/ and elsewhere) say that EAV is evil. Good in beginning but unscalable. IMHO is EAV acceptable e.g. for history tables where only some columns change and these tables are rarely used.

Maybe - maybe!! - you could implement EAV tables, but only for search purposes

select cols from main_table where id IN (select id from EAVattrs where attr_name = ...))

And store duplicit values for viewing in main table as XML fragment or somehow combined (?)

[allspiritseve]

Regarding to EAV, I have recently browsed various discussions and all people (http://www.dbforums.com/ and elsewhere) say that EAV is evil.

Yeah, that's the general opinion I've heard as well. Why, then, does Magento use it?

[Mastodont]

By reason that Magento's developers do not know best practices? Google returns many hits for combination "magento+slow"

Seriously - for low count of attributes this is feasible.

[allspiritseve]

By reason that Magento's developers do not know best practices? Google returns many hits for combination "magento+slow"

Seriously - for low count of attributes this is feasible.

Ok, so what would be the 'best practice' way of storing attributes? Would you recommend a table for each product 'type', so a new attribute = a new column on that table?

Or would you prefer a one -> many -> many relationship, where one product has many attributes, and each attribute has many values? (Again, I'd probably call these options if the user has to choose one). This scenario seems very similar to EAV, just split into two tables.

[oddz]

I would say that attributes represent a different product. A half gallon of milk is a different product than a gallon of milk. However, in order to relate the items I would use a abstract products table. I think a typical inheritance based table relationship would suffice.

abstract_products
-- id
-- name
-- description

products
-- id
-- abstract_product_id
-- name
-- description
-- price

In the previous scenario a half gallon and gallon of milk would both be "extending" abstract product milk. Yet, both the half and gallon of milk would be separate products.

Using a many to many relationship you could even implement a type of multiple "class" inheritance if needed although that doesn't seem the case.

Either way hypothetically the below would be represented in a table context.

abstract class Milk {
   protected $name = 'Milk';
   protected $description = 'Generic Description';
}

class HalfGallonOMilk extends Milk {
   protected $name = 'Half Gallon of Milk';
   protected $description = 'Override default description with this one';
   protected $price = 1;
}

class GallonOMilk extends Milk {
   protected $name = 'Gallon of Milk';
   protected $price = 2;
} 

[allspiritseve]

OK, so you favor what I've been calling 'Products' a product type. What about a large shirt vs a small shirt? Or a red can opener vs. a blue can opener? Are those all different products? How would you store all of those attributes (color, size, etc. assuming each product can have multiple values for each attribute and (potentially) multiple attributes for each product (though I only need one for this product).

[oddz]

hat about a large shirt vs a small shirt? Or a red can opener vs. a blue can opener? Are those all different products?

Yes, but both the red and blue can openers belong to a abstract product named can opener.

What about a large shirt vs a small shirt?

A large shirt and small shirt belong to the abstract product shirt.

(color, size, etc. assuming each product can have multiple values for each attribute and (potentially) multiple attributes for each product (though I only need one for this product).

Separately yet related via the abstract product which they belong to. It may seem like a lot but, in reality each of those items represents a separate product so I believe its appropriate to carry that real life scenario to the application.

[Cups]

How about:

A table row containing a "large red shirt" already contains its own unique key.

items
====
type : shirt
color : red
size : shirt

Just make all fields 3 unique, use it for the wine, use it for the can opener, use it for milk and chocolate milk. Could it work?

[allspiritseve]

How about:Just make all fields 3 unique, use it for the wine, use it for the can opener, use it for milk and chocolate milk. Could it work?

type : milk
color : chocolate
size: gallon
I suppose? Maybe?

I could do that for a limited number of products. But after a certain point, the table will have a ridiculous amount of columns and most of them won't be used in any given row. Thus some normalization is in order.

[Cups]

Actually I meant "Chocolate Milk" as being a separate product from milk, not a color attribute. Milk doesn't need a color does it?

I could do that for a limited number of products. But after a certain point, the table will have a ridiculous amount of columns and most of them won't be used in any given row.

Sorry, I was more musing than helping then.

Still musing then ... when I went back to your original post, I then thought that "color" is too specific, and is leading me the wrong way - how about "attribute"?

items
====
type : polo shirt
attribute : red
size : large

From the public perspective, that'd make nice, natural urls - which would be very easy to rewrite.

shop .com/polo-shirt/red/large

Be very easy to filter down to a select statement too, no de-referencing of id numbers.

Ah, but what if a product has more than one attribute (as well as size, which seems to be a fairly common requirement) ?

If a product has more than one attribute, then maybe it is actually a different product? (which is what I think oddz was getting at)

clothing
======
product : polo shirt with pocket
attribute : red
size : large

wine
====
product : Chateau Plonk 2003
attribute : red
size : 750ml

Here is a very long winded discussion where I learned about Functional Depency and natural keys which it looks as though I am advocating, or at least suggesting you consider rather than going for normalization. Apologies for the way it jumps around.

I don't know exactly how FD impacts upon domain models, but have a hunch it might make things easier.

One problem I can see right off is when a user wants to add a new "size", its not a matter of "select * from size" to show a droplist, which is what you'd normall(ized)y do.

[allspiritseve]

Ah, but what if a product has more than one attribute (as well as size, which seems to be a fairly common requirement) ?

I think that's the heart of the issue. How to deal with a variable number of attributes.

If a product has more than one attribute, then maybe it is actually a different product? (which is what I think oddz was getting at)

As attributes increase, number of products increase exponentially. If you have red, blue, and green shirts, and shirts with pockets and shirts without, you have a total of 6 products. Which is fine, but if you have a hundred products like this, a user is not going to want to browse through every variation of every product, nor is the client going to want to create a separate page with a separate description and a separate photo for every variation of every product. Of course this is on a spectrum: a site like newegg really doesn't have very many variations in products. If they do, they have a separate 'blue' or 'black' version-- a different product, as you said above. But you go to a clothing site, and there are many options for sizes and colors and styles for each individual product. If I'm making a system that caters to both types of sites, my instinct is to make the more flexible solution (the clothing site) and dumb things down for the simpler one.

Also-- making a new product every time something changes means duplicating all of the things that don't change. In objects I can extend a base class, but in the database the best tool I have for duplication is normalization.

Here is a very long winded discussion where I learned about Functional Depency and natural keys which it looks as though I am advocating, or at least suggesting you consider rather than going for normalization. Apologies for the way it jumps around.

I'll check it out.

[Czaries]

I think that's the heart of the issue. How to deal with a variable number of attributes.

As attributes increase, number of products increase exponentially. If you have red, blue, and green shirts, and shirts with pockets and shirts without, you have a total of 6 products. Which is fine, but if you have a hundred products like this, a user is not going to want to browse through every variation of every product, nor is the client going to want to create a separate page with a separate description and a separate photo for every variation of every product.

The complication here is compounded by the fact that each variation of the product needs a different SKU/UPC code so the order can be filled accurately. So even if only ONE thing is changed like color, all the sudden a new SKU must be defined because it's a different product. Nobody likes duplicating a bunch of products, but it's exactly the reason why that route is often chosen.

If you come up with an easy elegant solution to this problem, please let me know!

[Wardrop]

I was thinking recently of the best way to tackle the following problem, which is similar if not the same problem as you're facing allspiritseve...

If you have a product, which could have a variable number of attributes, a variable number of values per attribute, with each attribute value potentially decreasing or increasing the price of the overall product, how do you store this in a database? I thought of a couple of solutions straight up, but none of them achieved all the goals I was after. I eventually thought of the following solution which only requires two tables, while at the same time allows a lot of flexibility...

------------------
     Products     
------------------

ID
Title
Base_Price


----------------------
  Product Attributes  
----------------------

ID
Product_ID
Attribute
Value
Price_Diff
Order


ID	Title		Base_Price
--	-----		----------
45	T-Shirt		$15


ID	Product_ID	Attribute	Value		Price_Diff	Order
--	----------	---------	-----		----------	-----
384	45		colour		blue		+$0		1
385	45		colour		green		+$0		0
386	45		colour		orange		+$0		2
387	45		size		small		+$0		0
388	45		size		medium		+$1		1
389	45		size		Large		+$2		2
390	45		material	Velvet		+$5		2
391	45		material	Cotton		+$0		0
392	45		material	Polyester	-$3		1

As you can see, this allows one to have an unlimited number of attributes, and attribute values per product. It also allows any combination of attributes/value with dynamic pricing. I added the "Order" column just to demonstrate the extensibility. This column defines the display order of the values.

I'm yet to find any flaw with this setup (but please by all means try and find one). Querying the product attributes table should be reasonably quick as long as you have the first 3 columns indexed (or 4 depending on how you plan to use the table). On that note, I'm not sure how you would manage SKU's if every combination needed a unique SKU. I guess in such a circumstance where SKU's are required, you really would need to treat every attribute combination as a completely separate product. I also haven't thought of yet, how you could stop/block certain combinations (maybe an exclude column in the product_attr. table which references different product_attr. ID's?).

EDIT: I just thought of a possible way to handle SKU's without modifying the above solution to the original problem. All it would involve is having a separate SKU lookup table. This table would be relatively simple with only two fields. A side-effect of this also allows blocking of certain attribute value combinations if a matching SKU isn't found.

-------------------
    SKU Lookup
-------------------

SKU
Attributes


SKU	Attributes
---	----------
FH5636	384:387:390
FH5637	384:388:390
FH5638	384:389:390
FH5639	385:387:390
FH5640	385:388:390
FH5641	385:389:390
FH5642	386:387:390
FH5643	386:388:390
FH5644	386:389:390

The important thing to note here is that the order of the concatenated product_attr. ID values are alphabetical. This ensures that lookups are reliable. Alternatively, you can use the values of the attributes (ie. green:large:velvet) to achieve a similar result. Please let me know if there are any major flaws with this solution.

[r937]

any time you put more than one value inside a column, you are violating first normal form

and that's generally a very bad idea

[Wardrop]

It's not like we really have a choice in this case though. Besides, in this instance we're not treating the value of the "Attributes" column in the "SKU Lookup" as multiple values; we're using the resulting concatenated string as an index to lookup the SKU.

[matthijsA]

What kind of models and db schema's are chosen when using tagging systems? Isn't that about the same problem basically? You have thing A and that thing can have a variable amount of Tags.

You show a blog post and all related tags (or categories) are shown.
You show a product and all related attributes are shown

There's a lot of talk about Magento being slow and EAV models being "bad". But sites like delicious and Flickr seem to scale fine, isn't it? We should probably find out what kind of models are used in those (big) sites and learn from that

[Wardrop]

Isn't that about the same problem basically?

No, it's completely different. If the products we were dealing with had static attributes, then yes, essentially it would be the same. In this scenario though, the attributes have multiple available options, and each option can potentially increase or decrease the total price of that product.

[auricle]

the attributes have multiple available options, and each option can potentially increase or decrease the total price of that product.

In presenting the options to the user could you populate the page with a schema akin to blogging "tags" or categories? After all, wouldn't a specific product have a limited number of options?

As for the ordering with options, is that something that could be incorporated into the schema for the order?

[matthijsA]

No, it's completely different. If the products we were dealing with had static attributes, then yes, essentially it would be the same. In this scenario though, the attributes have multiple available options, and each option can potentially increase or decrease the total price of that product.

You didn't understand what I meant. I am not talking about products with static attributes. I'm saying, what if you see those variable amount of attributes the same way as tags (or categories) can be seen in apps like Flickr. Those tags are variable as well.

Flickr photo X:
Tag A, Tag B, Tag C, etc

Flickr photo X:
Tag A, Tag D, Tag F, etc

Product X
Attribute A, Attribute B, Attribute C, etc

Product X
Attribute C, Attribute D, Attribute K, etc

If you'd look at possible db tables:

Table Products (=Users)
1   T-shirt
2   Sweater
3   Jeans
4   Socks

Table Attributes (=Tags)
1   Color   Green
2   Color   Red
3   Size    2l
4   Size    1kg


Table Product2Attribute (=User2Tag)
1   1 
1   3
2   4
2   9
3   3 

There are other possible db schema's, but this is one possible

[Wardrop]

In presenting the options to the user could you populate the page with a schema akin to blogging "tags" or categories? After all, wouldn't a specific product have a limited number of options?

I'm sorry, that's not making much sense to me.

As for the ordering with options, is that something that could be incorporated into the schema for the order?

I'll assume you're talking about when a customer places an order, and not about the sorting order of the available options.

For orders, you'd have an orders table, an ordered_products table, and a third, ordered_products_attributes table. The orders table stores information about the order (eg. ID, Customer Details, Shipping, Comments), the ordered_products table stores the products that were ordered (eg. ID, Product ID,Name, Final Price), and finally, the ordered_products_attributes table would store the selected product attributes in a similar manner to the Product Attributes table mentioned in my previous post. It's pretty simply really.

[Wardrop]

I'm saying, what if you see those variable amount of attributes the same way as tags (or categories) can be seen in apps like Flickr.

Oh ok, I see what you mean now. I guess both methods would work. However your method I think would eventually fall apart, or become convulated. For example, if you were selling clothing, and one of the attributes you could choose was material type (or some other attribute that would increase or decrease the price of the product), what would happen if getting velvet material on pants, added an extra $10 to the total price, but only added an extra $5 to the price of a shirt. You'd get two "tags" (or attribute sets) which are exactly the same, but with different price modifiers. This could turn into a potential nightmare for whoever manages the product catalogue.

With my solution, you have complete flexibility as none of the attribute sets are grouped like tags/categories. If you're concerned from a user interface point of view, then keep in mind that you can still provide a form of multiple choice to the catalogue manager by simply generating a drop-down or a bunch of checkboxes from all the unique attributes in the Product Attributes table. In addition though, a textfield would always be available to enter any new or existing sets of attributes if multiple choice wasn't the users preference.

Not that it's a problem, but your solution also requires an extra table . In the end though, both solutions are very much alike, and I guess it would depend on the developers preference as to which one is or should be used.

[matthijsA]

Ok I didn't deal with prizes, yet.

However your method I think would eventually fall apart, or become convulated. For example, if you were selling clothing, and one of the attributes you could choose was material type (or some other attribute that would increase or decrease the price of the product), what would happen if getting velvet material on pants, added an extra $10 to the total price, but only added an extra $5 to the price of a shirt. You'd get two "tags" (or attribute sets) which are exactly the same, but with different price modifiers. This could turn into a potential nightmare for whoever manages the product catalogue.

I guess with prizes, I would put them in the product table. So if you'd have 20 variants of one product (say a shirt in 5 colors and 4 sizes), you'd get 20 rows in the product table.

Table Products (=Users)
1   T-shirt    $20
2   T-shirt    $22
3   T-shirt    $25
4   Sweater    $30
5   Jeans      $40
6   Socks      $10

Table Attributes (=Tags)
1   Color   Green
2   Color   Red
3   Size    2l
4   Size    1kg

Table Product2Attribute (=User2Tag)
1   1 
1   3
2   4
2   9
3   3 

So the T-shirt 1 is the most basic one, t-shirt 2 the one with a different fabric, etc

In other words, I would go the normal normalization route first. But I guess there are more solutions. I really have little experience with big e-commerce sites so don't know about the possible performance problems

[allspiritseve]

@matthjis I think that's essentially what Magento does- your attributes table is basically an EAV table. That's the most flexible solution, but as I understand it, is not optimized for searching (which is pretty important in an e-commerce site).

[Wardrop]

I guess with prizes, I would put them in the product table. So if you'd have 20 variants of one product (say a shirt in 5 colors and 4 sizes), you'd get 20 rows in the product table.

I don't think that would work too well. A product could potentially have over 10 multiple choice attributes, each with potentially over 10 values - assuming they all modify the price in some way, you start hitting 1000's of possible combinations (in my solution it would only store the 100 possible values, not all the combinations). Pre-calculating all of the combinations and storing them in a table is incredibly inefficient - but on that note, if you're dealing with SKU's that's essentially what you have to do anyway. But on top of that, how would you know which product/price goes with which combination of attributes?

I just see your solution as being more complex to achieve a less flexible result - in fact I don't think it would work at all when you consider variable pricing.