0

Let's say I have a Product and a ProductType table. I want a table where I can store special columns for each product type. The special columns (attributes) can be of any type.

Right now we have a table per ProductType called Product_%ProductTypeId% which is a solution I really don't like - any suggestions ?

My idea was to have a table ProductTypeColumns with cols:

Id | ProductTypeId | ColumnName | Value  | ColumnType

what I don't like about this is that I'm losing type safety, column Value would be a string type which would mean I have to always convert to and from.

Plus this table will be used in generating reports.. having "dynamic" columns may be a problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Narcis
  • 5,862
  • 3
  • 23
  • 30
  • 1
    EAV is often a way to do this, especially if these attributes can be different for a lot of products and if they can be added for new products frequently. I blogged about EAV here: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/11/19/what-is-so-bad-about-eav-anyway.aspx – Aaron Bertrand Feb 27 '12 at 20:06

2 Answers2

1

How about category hierarchy (aka. "inheritance", "subtype", "subclass")?

enter image description here

I'd personally go with a name/value pair solution (described in your question) only if the columns are not pre-determined.

If you know all columns in advance and they are unlikely to change, then implementing a category hierarchy, while not without complications, would preserve type safety and better enable enforcement of the declarative integrity by the DBMS (e.g. you could have a product-specific a UNIQUE, FOREIGN KEY or CHECK constraint).

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • I hope you're not implying that you can't have type safety in EAV. – Aaron Bertrand Feb 27 '12 at 20:57
  • @AaronBertrand I'm implying that you can't have type safety enforced at the DBMS level through declarative integrity constraints. If I'm wrong on that, then I wouldn't mind being enlightened on the topic ;) – Branko Dimitrijevic Feb 27 '12 at 21:14
  • 1
    Well, EAV doesn't have to mean `SQL_VARIANT`. If you read my article (linked above) I show how to have values of different data types where you can't just put a random string where a datetime attribute value was expected. Check constraints are also possible for specific attributes, e.g. `CHECK (AttributeID = x AND ISDATE(y) = 1) OR (AttributeID <> x)`. – Aaron Bertrand Feb 27 '12 at 21:33
  • The Partners_TypeId is kinda a category inheritance.. We are having a just the common types (string, int date time..) – Narcis Feb 28 '12 at 09:30
1

This is called EAV.
You can find some questions about EAV and SQL Server here on SO:

It's a subject where lots of different people will have different opinions.
Some will say that you should stick with Product_%ProductTypeId% tables, and some will say that your ProductTypeColumns table is the better way.

Basically, I'm in the ProductTypeColumns camp.
We are using something similar at work as well:
Our table contains attributes for order items (over a million order items and over 250 million attributes) and we are using it for nearly ten years now.
Disadvantages: no type safety (as you already mentioned) and querying can be a bit complicated.

But for us, it's the only possible way because we have over 1000 attributes, over 100 products and each product can have 100 to 300 attributes. With dimensions like these, an attribute table is the only possible way.
But it's probably not the best solution for everyone and I don't know if it's the best solution for you.

If your number of products and special attributes is not that large, maybe you can get away with your first suggestion (Product_%ProductTypeId% tables).
That would definitely be easier to query, but maybe it's not an option if there are too many possible combinations of products and attributes.

As often, it depends.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • Or a combination - there are probably some more important attributes that are common to many products (size, color, price etc.) that could be in one related table and the rest in an EAV. – HLGEM Feb 27 '12 at 22:26
  • i'm also worried about how hard it would be to make queries.. the queries would be very complex normally, adding this extra layer.. may be overkill – Narcis Feb 28 '12 at 09:33
  • @Zapacila: It's not that complex as soon as you get used to it. Concerning querying, be sure to check out [this link from my answer](http://stackoverflow.com/a/231681/6884) and [Aaron Bertrand's excellent article](https://sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway) that he linked in his comment above. – Christian Specht Feb 28 '12 at 10:19