Hello everyone,

My apologies if this has been asked before, but to be honest I wouldn't know what search criteria to use.

I've been struggling with a problem for the last couple of days and would really appreciate your input on this.
I am by no means a database expert, so feel free to point out any mistakes I am making.

I'm creating a webshop and have the following tables:

Products:
- productID
- categoryID (linked to category table)
- product
- description

Types:
- typeID
- productID
- typecode
- cost

So each product can have multiple product types, so far so good.
However, every product type can have multiple attributes.
These attributes can vary for each product.
The way I see it, there are 2 possible solutions:

Create an attribute table:

Attributes
- productID (I think this would make querying easier)
- typeID
- attribute
- value

This would mean I would also need a table defining which attributes exist for a product? (for instance to generate input forms I need to know which fields to create)

Structure:
- productID
- attribute

So with 5000 products, each having an average of say 5 attributes and 5 product types, this would mean 125000 records in the attribute table.

The other solution I have in mind would be separate type tables for each product instead of the one mentioned earlier:

types_[productID]
- typeID
- productID
- typecode
- cost
- attribute 1
- attribute 2
- etc.

This would mean dynamically creating tables and adding/removing columns on mutations.

This is the solution I chose at first and this works as it should (for now).
But somehow that annoying little voice in the back of my head keeps nagging that it's not "clean".

I guess I would rather go for the attribute table, but am a little worried about retrieving data (the queries would get rather complex I assume?) and performance in the future.

On the other hand having 5000 separate tables doesn't seem right either.

Any advice from the wise?

(Sorry for the long post, just trying to be as clear as I can)

Thanks for your time!

I'd go with the attribute table, but ONLY where it makes sense. if you have common information across the board store it in the the product table (things like price, availability, manufacturer etc.

but I think you need to balance the benefits of the attribute model against the disadvantages. if you need data integrity and data coherence use a traditional normalised design dont use the attribute model.

I've used this attribute model in a few applications, but I'm not claiming its wonderful or the solution to the 'the' problem (what ever 'the' problem is).

it works very well if you want to list attributes of various products, and you either don't know or don't want to give full details.

eg if you run a white goods electrical wholesaler you may have a 'pool'/table of products which stores common data (eg size, height, weight etc, cost & maker) and then have an attribute table detailing what each machines manufacturer has told you)

say your customer wanted to compare clothes washers, is a combi washer drier a washer, a drier or a product in its own right. the customer probably doesn't care they want to see what features the products offer. they may want to compare washer A drier D and washer drier X and expect you to display a table of data matching relevant attributes on the same row.

5000+ tables is a nightmare... don't even think of going down that route.
as to the amount of data in the attribute table.. does it matter what size it is if it stores the data that is needed, in a manner that is appropriate to the task.

First of all, thanks a lot for taking the time to reply healdem, I really appreciate it!

I understand what you mean, but in my case wouldn't that be stored in the types table?
For instance:

Products table:
Producttypes table:
Attributes table:
Structure table:
Am I on the right track here, or is this not what you meant?

Edit: sorry for messing up the layout, guess that doesn't work.

to me a product type is a category of product ferisntance
...etc

attribute Group would be things that describe the product, say it was a multi function printer
in the attribute table
id expect to see something like
print speed 10 ppm
resolution 2400 x 2400
or
resolution, horizontal 2400
resolution, vertical 2400

if the printer didn't have a modem, there would be no record of it in the Attributes table, or if you wanted to it could appear as N/A, or not available or what ever

theres a optional parent group so you can group things to gether logically
in my design theres also a sort order so that you could group together things in what ever sequence you decided was relevant

this model is fine providing you don't need to enforce data integrity and do need flexibility in what information you can store. doing comparisons can be tricky, but if you are repreating the information to the viewer than you don't need to concern yourelff too much. storing the attrivute data as a varchar allows you to decide if its numeric or character or date or whatever... the system doens't cre, becasue its not really significant in terms of how the information is represented. the down side is that its quite possible for typos to rep in

eg a line could get transcribed (its perfectly valid to store say 'yes' as the attribute data of print speed, or 10ppm as the resoluton). thats is soemthing you have to guard against. you could extend the attribute group definition to include say a format rule, or indictae what is an acceptable value

so you need to guard that the representation is for guidance and should not be relied on. and point customers to the manufacturers website for full details.

Well that is basically the way I have it right now, however I came to the conclusion that attributes (which I now have on category level and what you mean by product type) should be on product level.

In this webshop the following will occur more often than not:
Product X
- Type A: 100 pieces
- Type B: 150 pieces

Product Y
- Type A: Length 100mm radius 8mm
- Type B: Length 120mm radius 10mm

Basically there are a lot of products (mostly tools) that have different types/versions/materials/pieces/etc.
And when the user selects one product, the available types should be presented.
(If that makes sense).
So thats why I thought I needed to use both products and producttypes.
How would that fit in your example? Or should I just abandon the whole product/type setup alltogether?

Thanks again, for your help.

fine it may well be terminolgy

a product type to me identfiies the type of product, I'd suggest an optioanl parent category so you can apprropriately identify the type of products and where appropriate its family tree

certain information is common to all products (eg manufacturer, cost etc.. that sort of stuff in my books should be stored in the item/product table and if it must exists put a constraint on that element (eg cost must exist and must be more than...)

the attribute group is called group but it coudl just as equally be attribute type. that stores the variable stuff.

Got it, I think I know how to proceed (or rather start over) now.

Thanks a lot for your help healdem, I'm sure it will save me a lot of headaches in the future!

So after reading about every article/post I could find about this topic, I am back to where I started.

To summarize I (think I) have 3 options:

- EAV : However, the more I read about this, the more it seems that people strongly advise against this.
I read countless of arguments, complex SQL being one of the most named.
(Including the "heated" discussion on this forum)

- Supertype/subtype: My current setup which seems to be working fine, but this would mean I would get a table for every product, resulting in a huge number of tables once I get a large number of products.

I just can't seem to find that piece of information that justifies chosing either one of these options.
Which brings me to my third option:

- Using one big product table with a fixed number of attribute columns (e.g. att1, att2, att3, etc.)
They way I see it, this option has a few disadvantages:
1. Limiting the number of possible attributes to, for instance 10 (this wouldn't be a big problem imo).
2. All attributes will be of the same data type (NVARCHAR I presume).
3. Lots of null values (again, not a big problem, though not very "clean" either).

I am really at a loss here, it seems every one advises another approach, but it seems hard to find the "right" way (if there even is one, that is).

I would REALLY appreciate some more opinions on this.

I would NEVER go with individual tables for each product, you will drive yourself crazy!!

I am having a similar problem, in my case i have groups of products however i have to allow for infinite attributes for these. If uits of any help the way i am planning to go about it is:

Products_table
id
name (Honda acura)
description (Mint condition)
price
product__type_id

product types
id
name (car, motorcyle)

attributes:
id
name (number of wheels, tank capacity...)

product-to-attributes
product_id
type_id
value (4, 53 Gallons , etcc..)


if you can group your products into types, and these types have the similar attributes you could create a table products-to-types to simplify data entry. The other way you would have to display a dropdown with the list of attributes and then a field for its value.

hope this helps. Its helped me in creating a similar dbase. Good luck!

Why do you need an id field in the product types and attributes table?

I'm assuming that the name field should be unique in these tables, so it is perfectly acceptable to have this as the primary key (and the only field in the table!).

a table with only one column, a natural primary key, drives many developers nuts

That's what I thought, I decided to go with the third option (using one big product table with a fixed number of attribute columns).

The advantage (imo) is that I know beforehand what limitations and problems exactly, I'll run into.

The EAV model just seems to "un-databaselike" to me.