0

I need assistance to query from a model that contains one table of a specific object (i.e. Products) and one table of its dynamic attributes.

Let's say that I can store a Chocolate with attributes such as Price, Color, Weight and also a Car with attributes such as Engine, Gears, Color.

In my example I have a table called Products with the following columns : 

 Id (Int),   
 Name (NVarchar)

I have another table called dynamicAttributes with the following columns : 

Id (int)               -- of the attribute
ProductId (int)        -- of the specific product
AttributeType (int)    -- enum with the following values ("Color", "Price","Height", "Width".... )
StringValue -- of the product
IntValue -- of the product
DoubleValue -- of the product
BooleanValue -- of the product

I get from the client a list of attributes codes and a list of there values.

I can get value type (aka boolean, string, int) for each attribute.

What are my best options to query this model from my app ?

Dynamic sqls only ? Using Pivot keyword?

Bick
  • 17,833
  • 52
  • 146
  • 251
  • 1
    The name of this design anti-pattern is "entity attribute value". Search for it. There are numerous posts regarding this out there. –  Nov 18 '12 at 13:17
  • 1
    Really, really horribly bad idea - you should try to avoid EAV's - they *seems* like a smart choice at first, but over time, they become a huge performance and maintenance nightmare - not to mention the horribly inefficient and messy queries it takes to get any data out of them. .... **don't do it!** – marc_s Nov 18 '12 at 14:41
  • 1
    See [Avoiding the EAV of desruction (by Joe Celko)](http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/), [Five Simple Database Design Errors you should avoid (point #3)](http://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/), and [Bad CarMa](http://www.simple-talk.com/content/article.aspx?article=292) as an extreme example of a massive EAV failure – marc_s Nov 18 '12 at 14:43
  • @marc_s: So, in my example - Lets say that this is a website that sell those products. One vendor adds chocolate to my list and the other adds cars. The alternative is to add a table each time a vendor adds a product type. Somehow I don't think this is the way amazon works... – Bick Nov 18 '12 at 16:06
  • Well, if you **really** need this total flexibility (do you **really??**), then maybe a relational database is not the right choice for you. In that case, I'd investigate "NoSQL" databases like RavenDB or MongoDB as alternatives.... – marc_s Nov 18 '12 at 17:10
  • @marc_s: Thanks. By the example given (a site with vendors adding products is similiar to what i need ) I cant see a better way. adding a table for each request would be hard to maintain. So far I am using pivot a lot. – Bick Nov 18 '12 at 22:17

1 Answers1

1

As others have noted, doing a lot of PIVOT queries is pretty inefficient and it's laborious to write and to debug SQL queries that use PIVOT.

An alternative is to fetch the data back from the database in the way it's stored, i.e. in multiple rows. Then write code in your database access layer to massage the rows into a single object instance, adding one attribute to your object per database row. This is called the Table Module pattern in Martin Fowler's awesome book Patterns of Enterprise Application Architecture.

If you invest some time writing DBAL code in a reusable fashion, you may be able to make it pretty easy for subsequent code in your app to read and save objects stored in an EAV table.

But yeah, I agree with other commenters. I'm generally against using the EAV design. It takes a lot of work to write code to compensate for the ways EAV breaks database conventions. I would think you have better things to do with your time!

For alternatives, see:

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828