Storing product attributes/options in DB

[allspiritseve]

Branching off of this thread, what is the ideal way to store products in the database?

On a site I'm doing currently, I have a table 'products' and a table 'product_attributes' - attributes being things a user can choose before purchasing - size, color, style, etc. This setup works fine, but was built with only one attribute per product in mind, and doesn't allow the same attributes to be applied to more than one product.

I was discussing this in the other thread, and noticed that Magento (a php-based shopping cart) uses EAV tables to store their attributes. From previous experience and another poster, I know that's not an ideal solution. I'm wondering, though, what would be the 'best practice' way to store attributes for products, when each product may have any number of attributes, and each attribute many have any number of values?

[r937]

"best practice" for storing would have to be EAV -- it is the ultimate in genericness (if that's a word) and a pinnacle of simplicity

however, just storing data usually isn't da whole story, eh

it's getting it back out, specifically in searches, that's hard



picture the user interface for a search on shirts -- you'd want a dropdown for sizes, and another for colours, with maybe an options for all colours, text description search box, and so on

then the web interface would have to translate the form fields into query variables, and the query would have to go against the EAV table and retrieve multiple rows efficiently, and, more than that, combine them in the same logical ways as the user specified in the form...

and now do that with bike parts instead of shirts

as you said, "the setup works fine" but EAV has an exponential increase in difficulty the more sophisticated the retrieval needs to be

did that make sense or do i need to have another coffee?

[allspiritseve]

Ok... so what is the ideal way to store product attributes for retrieval? Preferably in a way that doesn't increase exponentially in difficulty the more products you have

[longneck]

let's use your example of size, color, style, etc., and extend it to a clothing store.

if you want the database to store stock levels of each combination of size, color, style, etc., then you really have no choice and will need one row per each possible combination.

if you don't need stock levels, then you CAN collapse multiple values for an attribute in to a delimited column. however, this only makes sense if you never want to let your users search for those values on their own. for example, if the only time you'll be displaying color is when looking at an item or a list of items but not using color as a search criteria to define the list, then delimited values might be OK.

but if you need to search those values, then you're back to one row per possible value.

frankly, i would almost always go with one row per possible value. if you abstract the products table using a class or set of functions in your application, you only ever have to deal with the complexity once.

i implemented this once and wrote a user interface that made it really easy to manage. the whole project also ended up being much easier to code than i had anticipated, once i wrote the abstraction layer to manage the products table.

[oddz]

Do the options affect the price or stock?

The major problem I see here is that if there are say 20 options for product how to handle the variant price and stock based on the selection of options. Its much easier just to handle each as a separate product if the options affect the price or stock individually and in different variations. Either way its going to become a mess as least by storing each product individually you only have one level to deal with.

[allspiritseve]

let's use your example of size, color, style, etc., and extend it to a clothing store.

I think that's a situation where most products will have a size, a color, and potentially a style. I'm wondering what to do in situations where the number of attributes vary in addition to the number of values each attribute has.

The major problem I see here is that if there are say 20 options for product how to handle the variant price and stock based on the selection of options. Its much easier just to handle each as a separate product if the options affect the price or stock individually and in different variations. Either way its going to become a mess as least by storing each product individually you only have one level to deal with.

I agree treating each variation as a product makes things simpler, but it also duplicates a ton of columns if only 1 attribute varies between any given two rows. Plus, to a client, a 'Product' is conceptually higher than a variation-- they will, for example, add a new Columbia jacket (product) and then specify the sizes for that shirt (variations/options/attributes on a product).

Maybe the answer is a table for each product 'type' (ie, 'jacket' or 'wine bottle' in which each attribute is a column on that table.

[r937]

I agree treating each variation as a product makes things simpler, but it also duplicates a ton of columns if only 1 attribute varies between any given two rows.

so? and this is a problem because... ???

analogy: you know how in an employees table you have many people with the first name "john" -- do you say to yourself "whoa, all that duplication from one person to another with the same first name, maybe we need to design another table which will hold unique instances of every first name, and link everyone to that table?"

no, you don't -- in fact, most developers never, ever think that this is a good idea

[allspiritseve]

so? and this is a problem because... ???

It's a problem because, unlike your employee analogy, each product variation is part of a 'product type' whereas an employee named john is not part of a 'john type'. If one john changes their name, they wouldn't all change their names, whereas if a product's name changed, or a variation's name changed, or a product ended up having different attributes, that would apply to all variations of that product, not just the one changed. I'm no DB expert, but in that situation my instinct is to move the duplication to another table so changing one row applies to all rows that reference that row with a foreign key. The reason, from what I've learned reading forum posts, is in case rows have data that varies (spelling errors, whatever) in this type of situation, they wouldn't get an applied change that referenced them by their value, rather than a foreign key. However, I will defer to your expertise if I am wrong.

[r937]

no, you're not wrong

a product type table isn't necessary, although in some applications it might have advantages

"instinct" in database design doesn't always produce the optimal result

[allspiritseve]

no, you're not wrong

a product type table isn't necessary, although in some applications it might have advantages

Well, I'm sure simpler schemas are fine for simpler sites, I just wanted to know for more complex e-commerce sites what direction I should head in, since I'd heard Magento used EAV and a lot of people didn't seem to agree with that decision.

"instinct" in database design doesn't always produce the optimal result

Exactly, hence two threads on the subject

[allspiritseve]

OK, so on a simple site we might have a schema like this:

product
--------
sku
title
description
size
color

And then on a more complex site, something like this?

shirt
-----
sku
title
description
size
color

wine
-----
sku
title
description
vineyard
type
volume

etc...

Is that where we've arrived?

[r937]

a separate table for every different product type?

i wouldn't go there myself...

[allspiritseve]

a separate table for every different product type?

i wouldn't go there myself...

Where would you go? A column on products for every unique attribute in the catalog?

[wwb_99]

I'd settle in the middle--columns for common product metadata, then a "payload column" of the specific product details. Personally, I'd use XML to store this in the database. Your product object can then be a bit polymorphic and act as the base products class or a concrete product type which would pull it's additional info from the XML.

Another way to model this is a base products table then a variety of general product type tables to hold attributes for specific types of products. A little less flexible than the above, but a bit easier to search on the database level if you want to go that way.

[allspiritseve]

@r937 if I have all my products in one table, I could see problems where a client might rename one product to the same name as another, and then any renaming would affect both products (assuming I'm distinguishing one product from another by their name, since two separate products may have the same attributes but should have a different name. Should I have a product_type table with the name on it, and then reference it with a foreing key from the products table?

[r937]

Should I have a product_type table with the name on it...

i don't think that would be necessary

[allspiritseve]

i don't think that would be necessary

Then how would you recommend differentiating two products with the same name and attributes? It's not an ideal situation, but if it can be done, a client will do it. With a product_type table I can have an auto incrementing id to differentiate them. Also, what do you do about columns that are shared between multiple products, such as a product title or description that is not affected by attributes? Do you just duplicate them across all products that need the same title/description?

Edit: I doubt you saw my question in the other thread, so I'll ask again here: do you have some links on where I can read up on when it is appropriate to move columns into another table and when to leave them in the first one? I'd like to better understand the tradeoffs involved in each decision.

[r937]

Then how would you recommend differentiating two products with the same name and attributes?

if they have the same name and the same attributes, then they're the same product!

Also, what do you do about columns that are shared between multiple products, such as a product title or description that is not affected by attributes? Do you just duplicate them across all products that need the same title/description?

i would, yes

the same way i would just duplicate the first name 'John" across all people in the persons table that have that first name that is not affected by the other attributes

do you have some links on where I can read up on when it is appropriate to move columns into another table and when to leave them in the first one?

search supertype/subtype -- there are some very comprehensive articles/tutorials out there

[allspiritseve]

if they have the same name and the same attributes, then they're the same product!

Not necessarily. If the user is going through and renaming a couple of products, there could potentially be duplicate names before they're done renaming. And the attributes don't have to be the same, as long as the name field is the one that determines a "Product with variations". Any name clash would result in rows for both "Product with variations" being edited at once.

i would, yes. The same way i would just duplicate the first name 'John" across all people in the persons table that have that first name that is not affected by the other attributes

I don't understand why you keep using that analogy. Multiple product rows have the same title/description, not because of coincidence, but because they're just variations of a single Product. Renaming one title/description should be renaming every title/description.

search supertype/subtype -- there are some very comprehensive articles/tutorials out there

I didn't mean in this scenario specifically, I just meant in general, when to create new tables and when not to. I'd always understood it to be to remove duplication, but you don't seem to care about duplication.

[r937]

I don't understand why you keep using that analogy.

because it is ~so~ appropriate

nobody every thinks that an employee table with dozens of duplications of the first name "John" warrants some kind of intervention by way of a FirstNames table with numeric ids

why? well, nobody does this because it would be silly... except it doesn't appear quite as silly in other contexts

if there are 15 different variations of "Kool Tshirt" based on size, colour, etc., then why is it necessary to abstract the description there?

suppose you want to change the description, well, you'd run something like this:

UPDATE products
   SET descr = 'Kool T-Shirt'
 WHERE descr = 'Kool Tshirt'

and vwalah, you're done

as far as duplication is concerned, let's suppose that you did go ahead and abstract the description into a product_type table

then each of the 15 products would have a link to its product_type table, yes? so the description is stored only once, right? well, my point about eliminating duplication is that you haven't eliminated it at all -- the 15 products all have the same numeric FK to the product_type table, so there is still exactly the same amount of duplication

did i explain that okay, or are you still not sure?

[allspiritseve]

because it is ~so~ appropriate

As I've said before, that's because they're not of a 'John type'. Renaming one shouldn't rename the others, but renaming a product 'Kool Tshirt' should rename all variations of that product.

if there are 15 different variations of "Kool Tshirt" based on size, colour, etc., then why is it necessary to abstract the description there?

It's taking up space, with no real need. If I wanted certain products to say 'Red Kool Tshirt' and have a separate description for each, then I would see the benefit to duplicating the name/description. The customization is there if you need it. But that's generally not a requirement.

well, my point about eliminating duplication is that you haven't eliminated it at all -- the 15 products all have the same numeric FK to the product_type table, so there is still exactly the same amount of duplication

duplicating 15 numbers takes up far less space than 15 names and full page descriptions of a product.

did i explain that okay, or are you still not sure?

I understand your point, not to eliminate duplication just because there's duplication. I think in this case the duplication is unnecessary, though.

[r937]

so it isn't duplication per se that normalization addresses, is it

now let's move on the the pros and cons of abstracting the product description

one of the benefits is that it takes less space (although to be frank, disk space is ~not~ an issue at a few dollers per gigabyte, is it)

can you think of any drawbacks for the product_type (description) table?

[allspiritseve]

can you think of any drawbacks for the product_type (description) table?

It requires a join, and doesn't allow you to change individual product names/descriptions separate from the product_type table. Anything else?

[r937]

Anything else?

ain't that enough???