Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
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: Wednesday, May 20, 2015 6:21 AM
Points: 49, Visits: 109
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

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:

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

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
Posted Monday, June 8, 2015 8:37 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 2:42 PM
Points: 3,370, Visits: 6,889
I inherited a large EAV (TRANSLATE_CODE_VALUE) that contained descriptions matched to a series of single-character codes across dozens of columns in several primary tables. The schema (and primary key) was ColumnName, Code, Description. One table had to join to the descriptions table nearly 20 times and performance obviously suffered. The surprisingly satisfactory solution was to write a procedure to convert all of those codes into a set of inline table valued functions, each consisting of a single CASE statement (see below).

It only takes a second or so to regenerate the entire set when the descriptions table is changed, and the query plan benefitted greatly from replacing joins to the EAV with CROSS APPLYS to the newly generated functions. Essentially replacing a lot of I/O with simple compute scalar operations.

This solution also preserves the only rational function I've ever had to concede for an EAV, NVP table, which is to keep a common repository of all definitions in one place for maintenance.

Below is a sample function that was created automatically by the translate procedure. The text description is also automatically generated to warn off future generations from making changes directly to the function rather than continuing to maintain the TRANSLATE_CODE_VALUE table.

	ALTER FUNCTION [dbo].[tfn_TRANSLATE_APPR_ASSIGNMENT_TYPE]                    (@input varchar(10))
-- This function was automatically generated by dbo.stGenerateTranslateFunctions on
-- Apr 16 2014 11:37:20:807AM (Pacific). All codes and their translated long values
-- come from [dbo].[TRANSLATE_CODE_VALUE].
(SELECT CASE ltrim(rtrim(@input))
WHEN 'O' THEN 'Other'
WHEN 'P' THEN 'Purchase'
WHEN 'R' THEN 'Rental'
ELSE '??'
END as output_text_long);

Admittedly, this function could be improved on for cases where dozens of codes existed for a single column. Nested CASE statements could emulate a binary search to minimize the execution time of functions with a truly large number of codes. However the performance has been more than adequate so far without that modification.

Edited to add that yes I know this thread was almost five years old when I added this.


Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Post #1692501
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse