Alternative to EAV model

Question

Alternative to EAV model

Bhushan Kulkarni

SSCrazy

Points: 2829
More actions
December 21, 2015 at 5:00 am

#310286

Hi,
In our project, we have requirement such that user can set user defined properties for Entities- these properties will be dynamic.
So we implemented it using EAV model.
Base table is BaseEntity which stored basic information of Entity such as Name, createddate, createdby,BusinessEntityType etc.
And BaseEntityAttribute table stores dynamic properties for each entity in key value pair.
User can set upto 500 properties for each entity and on an average each business entity has 25 properties.
So this is causing performance issues when we retrieve data for Particular Business Entity type. We are using Pivot while retrieving data.
We cant not use XML data type as User may add new property or delete any existing property.
Is there any alternative in SQL Server 2008R2 or 2012 version.
Or we need to look to for any NoSQL Database.
Please give your suggestion that will really help us.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Bhushan Kulkarni (12/21/2015)
Hi,
In our project, we have requirement such that user can set user defined properties for Entities- these properties will be dynamic.
So we implemented it using EAV model.
Base table is BaseEntity which stored basic information of Entity such as Name, createddate, createdby,BusinessEntityType etc.
And BaseEntityAttribute table stores dynamic properties for each entity in key value pair.
User can set upto 500 properties for each entity and on an average each business entity has 25 properties.
So this is causing performance issues when we retrieve data for Particular Business Entity type. We are using Pivot while retrieving data.
We cant not use XML data type as User may add new property or delete any existing property.
Is there any alternative in SQL Server 2008R2 or 2012 version.
Or we need to look to for any NoSQL Database.
Please give your suggestion that will really help us.
Thanks

We could probably offer some suggestions but it would require you to first offer some details. This post is so short on any real details that best you are going to get is some people guessing at what you are trying to do and how to improve that which you didn't share details about the current implementation.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

Alternative technical approaches aside (I'd at least give 3NF a fair shot), first challenge whether an EAV solution is needed. I would interview the users and run some aggregate queries against this BaseEntity data model and confirm if creating entirely new attributes on the fly is really what they're doing, or if they're re-creating essentially the same attributes under different names as a result of lack of design and coordination.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Bhushan Kulkarni (12/21/2015)
Hi,
In our project, we have requirement such that user can set user defined properties for Entities- these properties will be dynamic.
So we implemented it using EAV model.
Base table is BaseEntity which stored basic information of Entity such as Name, createddate, createdby,BusinessEntityType etc.
And BaseEntityAttribute table stores dynamic properties for each entity in key value pair.
User can set upto 500 properties for each entity and on an average each business entity has 25 properties.
So this is causing performance issues when we retrieve data for Particular Business Entity type. We are using Pivot while retrieving data.
We cant not use XML data type as User may add new property or delete any existing property.
Is there any alternative in SQL Server 2008R2 or 2012 version.
Or we need to look to for any NoSQL Database.
Please give your suggestion that will really help us.
Thanks

Quick question, why can't you use XML?

😎

Suggest you look into sparse columns, supported by SQL Server 2008R2 and later, very efficient way of handling this and no need for pivoting the data.