Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

EAV's and NVP's are bad. So... What do YOU use as an alternative? Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 1:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:06 AM
Points: 49, Visits: 105
I've seen and used EAV in several cases and, like you said, it's all in the context of the data that is being stored.

In short, if the data points are known beforehand, tend to stay static and are well defined then simply use a relational model with proper normalization.

On the other hand, sometimes even the customer cannot know in advance all of the possible future data points they may need to track (that is why the NIH/CDC uses an EAV model for tracking diseases - you can't know all of the variables in advance). In this case, an EAV structure allows new datapoints to be added at-will without invalidating older data (since the older, related data will simply not have the newer attributes).

There is naturally a concern with those terrible End Users adding attributes to the EAV store willy-nilly... that's why you restrict the attribute definitions to an administrative screen with limited access.

I can't tell you how many databases I've run across with hundreds if not thousands of columns for a single entity which were mostly filled with NULLS and required expensive program rewrites every time a new data point (column) needed to be tracked. EAV fixes this and makes searching a snap. Reporting can be a bear, like others mentioned, because of the need to 'flatten' the data and yes, there is a performance hit as well, but when Scalability outweighs performance issues, then EAV/NVP may be the way to go.

Just like Indexes are a fantastic way of speeding up queries, adding 100 of them to a single table is probably a bad idea and makes things worse instead of better. Everything in moderation, use the right tool for the job at hand.
Post #1377259
Posted Friday, August 2, 2013 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 2, 2013 8:10 AM
Points: 3, Visits: 1
Hello,

My problem is consisted of the subjects where one part of the subject also
should be used EAV modelling technique.

But have many opened questions: http://www.sqlservercentral.com/Forums/Topic1480416-373-1.aspx.

How to prepare the database so the validation in form fields in application
are dynamically generated when new attribute is defined.
And same way when displaying atribbute value in reports ( value masks ).


Another feature is how to deal when type of the attribute is such that values
are referenced from some simple entity ( table ) let say with
primary keys consisted of 1, 2, 3 columns. So the value of the attribute is
vector?

I need to make some limitations to the project not to modell somethign that
would make mass in the application code and hard to maintain?
Post #1480424
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse