InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:15:44 GMT20http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxHeh... c'mon.... EAV's and NVP's are cool. You can put the entire database in one table and you only have to worry about maintenance on one index. :-P:-P:-PMon, 17 Aug 2009 10:42:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxGreat discussion here.I'm having a similar dilemma. We are building a social component to our website and the requirements are that customers and or external application would be able to create a profile in our system with user define fields.I've created a profile table with the basic info. firstname, lastname, photo etc...Now I'm trying to figure out the best way to include the additional user defined fields and is where the EAV model came up. I've read in numerous blogs/sites to not do it unless absolutely necessary.From these readings, I have not found that anyone has a good alternative. There is always some major deficiency in it.Would it make sense to store the user defined fields in an xml column? or in my case would EAV work efficiently where i would only store a subset of the data in an EAV model?The other option I have is making the middle layer do the column manipulation. So if I went with the EAV model, my select would only be ...select ProfileID, Entity, valuefrom EAV_Profilewhere Profileid = 1an not the horrible select with numerous joins and case statements. The rest of the formatting and logic would happen in the mid layer. Does this seem likeAny additional advise would be greatly appreciated.Mon, 17 Aug 2009 10:04:36 GMTSQLDev1http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxFrom someone that is asked to support add hoc reports generated on an EAV it is a difficult exercise to convince end users why certain types of reports are fine and others aren't. Its a way too technical distinction.Most non-technical are either happy with the concept of a linear performance degradation as per flat file scanning. Or expect a logarithmic performance degradation as per effective b-tree indexed relational database.Having a EAV limits indexing possibilities. SQL2005 pivot requires the column names to be defined in the query which makes it unsuitable for EAV. (any usefully implementation has dynamic Attribute set not determinable until query execution)If you know the Attributes of the entity prior to query execution then persist them into the database schema as columns. Sure an index only sought and returned a record in 2005 is efficient. But it is not as trivial returning an additional extra couple of hundred bytes of an additional column on the same table.Thu, 07 Aug 2008 20:42:50 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspx[quote]I do understand that an Index on this theoretical "Bitmask" column would be expensive to maintain, but how is Indexing a Bitmask column any different than say a "LastName" column? Is SQL any smarter when indexing millions of Patients' last names? In other words does SQL interpret your query and go 'Oh, you're looking for smith, ok, find all names that start with "S" and then break that down into "mith"'? [/quote]Basically, yes. The problem with a Bitmask in general, and with indexing a bitmask is that you rarely want the whole bitmask, you want to know whether some bit is set, but that isn't how indexing works. (Now, if there was a way to mark a bitmask as such and SQL would then index it differently that would change things).[quote]Mathematical calculations have always been superb in SQL, so I can't see how "Select ID From TestAnswers Where Anwser & Power(2, 2 - 1) = Power(2, 2 - 1)" (2 = correct answer) is any different than when we are doing summations on Monetary columns? [/quote]It is different in the same way that using SUBSTRING is different. It is easier for the engine to find all words that start with "A" than to find all words that have "A" as the 3rd letter. In the same way it is easier for the engine to find all numbers greater than 50 than to find all odd numbers.[quote]Also, as far as reporting goes, yes, you do have to build views to give a better representation about the data in an EAV. However, that IS the main point of an EAV! EAV's give you the capability to report on the same data in a myriad of ways without the need for extra columns, changes in schema, or redesigning an application.[/quote]Er.. No. The point of an EAV is to allow the customer/user to define their own data structure. You can then build a cumbersome reporting assembly over top of this to allow for ad-hoc reporting if you desire.You can always report on the same data in many ways using a database, that is the point of the database. Data Structure helps you to determine whether the reporting you want actually makes sense with the data you have.--JimFiveThu, 07 Aug 2008 07:59:21 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxSomewhat, but not that bad. Even so, because the EAV tables are usually very narrow (meaning, only a few columns), there's usually 1 or 2 indexes that do the job very efficiently. So, LEFT JOIN-ing the same table multiple times to essentially "PIVOT" the information horizontally has never been a huge performance hit for us because it always hits the Indexes in the execution plan as opposed to doing full table scnas for every JOIN. Now, you might get away with newer techniques in SQL 2005 being more effecient like the use of CTE's and the new PIVOT function (although I've never had huge success with the PIVOT function).I have since been using an Xml column for most of my EAV models now that SQL 2005 gives awesome support for breaking out Xml. But in a sense it's pretty much the same except there are no multiple JOINs.Thu, 07 Aug 2008 07:45:41 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxNot specifically commenting on the bitmask approach but with the more general mechanismdont your views end up as massive nested statements. ie: select (select value from EAV where id=? and name='Title') as Title,(select value from EAV where id=? and name='Forenames') as Forenames,(select value from EAV where id=? and name='Surname') as SurnameWhere performance is always going to be n * worse than a standard view where n is the number of columns retrieved. Or am I missing something?I still maintain that database engines are very going at doing this kind of operation under the hood but poor at surfacing it via SQLThu, 07 Aug 2008 07:33:14 GMTTrevor Scurrhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI do understand that an Index on this theoretical "Bitmask" column would be expensive to maintain, but how is Indexing a Bitmask column any different than say a "LastName" column? Is SQL any smarter when indexing millions of Patients' last names? In other words does SQL interpret your query and go 'Oh, you're looking for smith, ok, find all names that start with "S" and then break that down into "mith"'? Or does it just look at the name in it's entirety. Isn't SQL supposed to be smarter when looking for numbers? Mathematical calculations have always been superb in SQL, so I can't see how "Select ID From TestAnswers Where Anwser & Power(2, 2 - 1) = Power(2, 2 - 1)" (2 = correct answer) is any different than when we are doing summations on Monetary columns? True, you might not Index all columns you want to sum on, but when I want a query that is like "Give me all Sales where the Total Price was >= $500". How is this going to be any better?Also, as far as reporting goes, yes, you do have to build views to give a better representation about the data in an EAV. However, that IS the main point of an EAV! EAV's give you the capability to report on the same data in a myriad of ways without the need for extra columns, changes in schema, or redesigning an application. I never report from tables anyway so I've never been limited in this factor and I have been using Reporting Tools for over 10 years (Crystal Reports 6 - 11.5, Reporting Services, MS Access, DevExpress XtraReports, etc, etc).I'm not trying to be snoddy, I truly want to understand. :DThu, 07 Aug 2008 07:03:24 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI remember building a COM object based system where the com objects represented entities which corresponded to tables in the database. To avoid having to change the interface of the com objects everytime we wanted a new data item we implemented a EAV model in the database table and exposed this through generic methods. In the end performance and the programming model were compromised.At the end of the day what you are doing is replicating what the database engine is doing under the hood at a higher level. Databases are good at referring column names and tables and constructing queries based upon them. Its also not generally a problem to alter tables to reflect user requirements (although the application model cant be rigid as in above), or to have specific table constructs to handle user data etc. One additional aspect I have not seen covered is reporting. Every tried to stick a third party reporting tool onto an EAV model? Not pretty. You end up having to build views to construct virtual tables based on the EAV tables and performance takes a further dive. As has been pointed out where EAVs do work well is in auditing and event reporting. Auditing especially works well with a table, column, oldvalue, newvalue type of EAV, but this is a specific case. In general I would stay well clearThu, 07 Aug 2008 05:48:11 GMTTrevor Scurrhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxDavid,You are, of course, correct. The best way to store a choose-many would be just to record each answer in a table like: SurveyID, QuestionId, AnswerID [AnswerText]. Allowing each SurveyID, QuestionID to have multiple answers. You then need to do some checking on the choose-ones to make sure that you don't accept multiple responses for those questions.I found that the bitmask method made it easier to create the data entry form at the cost of more complicated reporting.--JimFiveThu, 22 May 2008 06:17:10 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxSurveys are Frequently collected for statistics.Bit mask seems like a poor choice. You'd force the query plan to scan the bitmask field to look for all surveys that answered question 2 false. (think about any bitmask fields sort order in its index)If there were multiple clients querying for the current total of people that answered Q2 false .....Wed, 21 May 2008 23:29:28 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxGood input. You are right, it is 1-to-many, I probably didn't phrase that correctly. I meant it is somewhat of an EAV, but instead of being so arbitrary (as EAV is), it's become specific to it's purpose. My example was bad but I wanted to note that you didn't need a table per Culture, simply one table that stored the various descriptions and maybe a Culture column on it.As for your design, I can't believe I didn't think of storing the T/F as multiple choice and making the multiple choice store as a bit mask, this makes great sense!So if I had:How much wood can a woodchuck chuck in an hour (multiple choice)?1 - five (bitmask = 1)2 - four (bitmask = 2)3 - ten (bitmask = 4)and if the user selected all then the value would be stored as: 7Select Power(2, @Answer1 - 1) | Power(2, @Answer2 - 1) | Power(2, @Answer3 - 1)Or simplySelect Power(2, 0) | Power(2, 1) | Power(2, 2)Or evenSelect 1 | 2 | 4Then, to check which answers the user selected:Select @Answer & Power(2, 1 - 1) = Power(2, 1 - 1) As DidYouSelectAns1,@Answer & Power(2, 2 - 1) = Power(2, 2 - 1) As DidYouSelectAns2,@Answer & Power(2, 3 - 1) = Power(2, 3 - 1) As DidYouSelectAns3Or more simplySelect @Answer & 1 = 1 As DidYouSelectAns1,@Answer & 2 = 2 As DidYouSelectAns2,@Answer & 4 = 4 As DidYouSelectAns3And if you want to validate against the correct answer@CorrectValue = Power(2, 2 - 1)If ((@Answer & @CorrectValue) = @CorrectValue) Print 'You are correct'I hope my examples aren't too confusing, this isn't a discussion on bitmasking, but I want other readers to understand the power of it for storing answers to T/F or multiple choice questions. In a T/F question, the bitmask values are simply 1(T) or 0(F).Wed, 21 May 2008 02:44:20 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspx@tymberwyld[quote]When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.[/quote]No, that's 1 to Many. EAV abstracts out the type of the data. If you have a table like: StringID, LanguageID, DisplayText to handle internationalization, that is not EAV.If you have a table like: EntityID, AttributeID, AttributeValue where the various attributes addressed by AttributeID are unrelated and/or AttributeValue can contain multiple types of data then you have EAV.When you get on a roll normalizing your database and abstracting everything, it becomes tempting to keep abstracting until you end up having abstracted the database out of your data, that is where you find EAV.Having a few untyped fields for the customer to use for reporting is acceptable, but building your database that way isn't.@JJ BAs has been mentioned, your survey design isn't EAV (under most circumstances). It is normalized. My survey databases look very much like that described by tymberwyld with a couple of caveats:There are two multiple choice types: Choose One and Choose ManyTrue/False questions are multiple choice questions (Not special). This allows for No Answer and Not Applicable responses.I have two value columns, one for choices and one for free texts.To handle Choose Many I set the "value" associated with a response as a power of two and then store the sum of the selected values. This is a little more work on the front end but allows for easily finding surveys with the exact same answers. It does make it more difficult to find "all surveys that selected B", but not that much.Also note that my survey sets are very small so I don't notice any performance problems that may exist.--JimFiveTue, 20 May 2008 13:14:20 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxInteresting discussion. I certainly see where EAV design is effective for front-end systems which expect to receive large numbers of unknown values from multiple sources or when the query objectives are for display of a single response set (to display a risk score on the screen, for example). The time and resource savings for redesign of data models are evident. Great for "getting data in". However, what this practice really does is delay the integration decisions to a later step in the process. We data warehouse developers struggle with name value pair design in our front end (source) systems. One of the primary purposes of a good data model is communication of the business relationships among the logical entities about which the business has an interest. The EAV model cannot be used as such a communication tool. On the data warehousing side and the downstream reporting side the intent is to make the data available to end report users and decision makers in an unambiguous easily queryable form that is tuned to return millions of rows in aggregated form. We are tasked with analyzing and dissecting the value pairs and creating a logical business model that is representative of the integrated content. Its really a case of pay now or pay later.We took an approach in our datamart (star schema) where each fact record represented an assessment and had a FK to our question table for each question. This of course reduced the number of storage rows by raising the grain from a single response to a set of responses, but had the negative and limiting effect of creating multiple joins, which at 24 currently is slowing performance not to mention confusing our OLAP Reporting tool developers. We will now create a view that performs the the joins for them, but I am still searching for an approach for the survey problem that combines reporting performance and business clarity with the lowest possible storage requirements. Look forward to T.Asfaw's second article and hope you will address the impact of EAV design on the downstream Data Warehousing and Reporting arenas.Wed, 30 Apr 2008 08:32:46 GMTdeborah_griffinhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxtymberwyld: Thank you for taking the time to reply. So much to think about....Mon, 31 Mar 2008 09:12:16 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxThis is exactly what I was talking about in my previous post. You might be able to represent this type of Client-Question-Response using an EAV, but you'll find it's better to have a good relational model with "limited" EAV-ness. The only EAV portion I can see in your model would be the Client "Answers" or "Responses" table.Basically there are two pieces:1.) The storage model for the Surveys, Questions, and Answers:[b]Surveys[/b] (table)[b]Questions[/b](table) - this table stores the Question, type of Question (multiple-choice, true / false, or written). When the question type = "multiple choice" then the Answers table will be used. You could also provide a "Score" column here that would represent the overall points associated with this question if it had more "weight" than others (in the situation where this is a survey type of "Test / Exam"??).[b]Answers[/b] (table) - this table stores the answers, their sequence (so they can be re-ordered) and any other info about them. This table is probably only used for multiple-choice questions. You could also provide a "Score" column in this table if you're trying to derive a total "risK assessment" type of survey.2.) The storage model for the Surveys, Clients, and their Responses / Answers. In this side of the design, you are sort of using an EAV on the "ClientAnswers" because you're not sure that the "Value" column is storing an answer to a True/False or a Multiple-Choice question.[b]Surveys[/b] (table) - same as above[b]ClientSurveys[/b] (table) - stores which surveys the client has taken. Maybe a total score if you're not always summing the score from the ClientAnswers.[b]ClientAnswers[/b] (table) - stores the answer the client chose for the particular question in the survey. This is a child of [b]ClientSurveys[/b]. In this table, the "Answer" (or "Value") column should probably be a Sql_Variant. This way you can either store the Value from one of the multiple-choice answers (an Int usually) or the "T" or "F" as a Bit data type. Actually, I'd have two valid "Value" columns (Value Sql_Variant NULL, and ValueText (Text or VarChar(MAX) NULL). This way if one of the question is a "written answer" then the storage or the answer will be more efficient because only for a limited number of records will the Text or Varchar(MAX) column be used.I hope I haven't confused you too much...Sun, 30 Mar 2008 16:10:14 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxDavid: Thanks again for your reply. You did a good job of laying out a decision framework / set of criteria to use for making these kinds of decisions. Nice.Thu, 27 Mar 2008 09:27:30 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI would suggest that the EAV is where the types are taken out of the database entirely and only the application context would be able to decipher the significance of what entity type id x means.Which is fine when the application doesn't want to 'view' the data sorted by something other than the order that IS in the data model.(eg clientid order). Getting the answers for a customer; doesn't need the question types in the schema and has the advantage that if the customer has another set of 'typed' data that needs to be stored that there is no schema level change. (or change to SQL to extract for this customer survey)For example entity response type id 1-32 could be the original question responses that the application stored about the client. You could just as easily create response entity ids 33-66 that store a physicians responses to a different set of questions that had a contextually different meaning determined by the application (but about the same client). This would be to say that the database schema did not describe the applications data model or rather the bit of it where 1-32 means client responses and 33-66 means physicians and the application / stored procedure could then extract the two contextually different data sets internally knowing by id what the responses meant. Great so we have just allowed for the application to have the flexibility to change its data model without any change to db schema. This is at the cost of needing to recreate the part of the data model that is not in the schema each time we present it logically to the user. For simple parts like a flat list there is little effort or even no schema to be reconstructed in producing a flat list for a specific range of a larger flat list. For more abstracted types the reconstruction becomes non trivial.As you add more and more abstracted types (and layers of abstraction) you end up reducing the selectivity of the index (and increasing the number of seeks by the level of abstraction). For example the selectivity of the and client-question-response index by question id. At some number of layers of abstraction and amount of data being indexed the work reconstructing the actual types becomes measurable. I doubt a single abstracted type is going to do anything measurable.An EAV becomes a target for a change to a indexed denormalized relational structure with schema defined types (tables) similar to your proposal, if the application ends up with time critical grouping demands on the E's for a large number of V's in a grouping order very different to the order indexed (or initial grouping). Ie the ability to view clients by their id is as important as clients by their response to question 1 a survey done in 2005.Alternatively if you are happy that the relationships between the client type the question type and the survey type are unlikely to change then putting it into the db schema only adds the risk of needing to change the schema (as well as the application change which would be mandated anyway) if these relationships change in their actual as use. Wed, 26 Mar 2008 19:21:14 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxDavid: I've been thinking more about your reply. Particularly, I've been caught on your first sentence: "Aren't you just normalizing the question type out of the client-question-response?"That's such a good point. So, is my proposal good normalization or EAV? At what point is something EAV vs good normalization? I understand that the answer relates to the content/subject of the data. But I think it is worth thinking about.Wed, 26 Mar 2008 14:47:28 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxDavid: Thanks for your reply!To answer one of your questions, "Would you look for clients by their responses across survey's?" - At the moment, I don't see that happening for particular questions, but you never know. At the moment, the main purpose of the survey would be to define a total risk level for the client. If the client's risk level is say "high", then the investigator/user would know to do certain actions to help the client. I also imagine the users wanting annual summary stats such as: How many clients last year had a risk level of low? med? high?You do not have to explain, but I thought I would let you know that I didn't understand this part of your reply: "But if Question,Person and survey were normalized types out of some entity table, and person-has-a-survey, survey-has-a-question and Person-takes-a-survey-question where normalized types out of some entity-relationship table then I would start to squirm."As for this part: "If you know that the client takes a survey not a supervisor or an operator...", I'm not sure I understood your full point, but I can comment on this one part. The clients themselves will never actually be filling out the "survey". Instead, the operator (our investigators/staff) will decide if a survey/assessment is required. If so, then a completely survey will likely be set up for that client, by which I mean a stored proc will likely be called to generate a record for every active question and attach to that client. The default answer for each question would be null or "not applicable", and the user would just change the answers for the questions that apply. For now, I think that set-up would allow me to create the most user-friendly interface.Thanks again for your interest.Wed, 26 Mar 2008 14:12:43 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxAren't you just normalizing the question type out of the client-question-response ? consider finding a client that has[quote]have no Risk of a fallhave low Risk to client or caregivers from pets, livestock, etc.have high home odors (gas, rotting food, etc.)have no skin conditionhave low access to adequate medical supplieshas no recall recent of events[/quote]Would you look for clients by their responses across survey's ?If they didn't answer that question should that mean that they are still eligable to be returned if your search was only looking for clients with no risk of fall ?I would not be scared of this type of normalisation with client,question and client-question-response covered in appropriate indexes and only a couple of million responses.If you were proposing to have client as an normalised type out of say person I would still probably not.But if Question,Person and survey were normalized types out of some entity table, and person-has-a-survey, survey-has-a-question and Person-takes-a-survey-question where normalized types out of some entity-relationship table then I would start to squirm.If you know that the client takes a survey not a supervisor or an operator and that the survey is the only thing that has questions that you need to search for clients by. Then denormalising into the database foreign key relationship the survey-question-response increases the scalability possibilities, specifically the case of clients that have no risk of fall type search. And has low, if not no risk to the flexibility of the application. Anyone else ?Tue, 25 Mar 2008 18:59:51 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxThanks for the article. I'm interested in understanding different design schemes. My question may have already been answered, but I'm not sure.For the first time, I'm considering an EAV setup, and I would be very interested to hear opinions. My staff need to do a "risk assessment", but you can also think of it as a "survey" problem. I think there is a very good chance that the questions in the assessment/survey will vary over time as we refine our assessment process. Here's a subset of some of the questions in the risk assessment to give you a sense of what I am talking about:[quote]> Risk of a fall> Risk to client or caregivers from pets, livestock, etc.> home has odors (gas, rotting food, etc.)> client has skin condition> client has access to adequate medical supplies> client can recall recent events[/quote]The answer to "every" (as least this is what is claimed is true not only now, but in the future) question is "none, minor, moderate, Severe, or n/a". Also note that the questions/assessment categories are grouped together. For example, there are questions under the categories of: physical condition of the client, availability of shelter, hazards, sanitation of the home, etc.Each answer has a point/percentage implication for the final "risk assessment" for that client. I'm not sure yet of the formulas, but I think the application may have to do things like: "for every question in this category of questions (say for all the "hazards" questions), give 5 for every "minor" answer, 10 for every "moderate" answer, 15 for every "sever" answer. Then add it all up. Taking all that in, I was thinking that the following type of schema might make sense. But I'm nervous about it in light of this article. Note that this is bare-bones. I'm not trying to define the schema completely. I'm just exploring the idea of the basic set-up.Table: QuestionListqID - number for each questionqStartDt - when question becomes validqStopDt - when question is no longer validCategoryCd - link to another "lookup" table that lets us group questions according to a categoryqDescription - the actual question (examples are given above)Table: Assessments (the main table, with EAV)ClientID - link to the client getting the assessment. I would expect a set of qID records for every client getting assessed.qID - link to above tableAnswerCd - link to another "lookup" table that gives us the answers listed above (minor, moderate, etc). That lookup table would also include info for calculations.This is a simplified version of what I would do, but the basic idea is there. It seems to me that this would be easy to query, do formulas, and not be such a bad performer. Also since the answers all follow the same format, I can do good data validation/restriction. But I do not have much experience with this type of design, especially with the amount of usage the design could potentially get.Which brings us to this note. I'm not sure of the usage for this data. I would think that worse-case would put us at about 4 million records after 10 years. But that is only if the program ends up getting much wider usage than my little agency.Thoughts?Tue, 25 Mar 2008 12:14:14 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxJust like to thank everyone for an excellent discussion. Very informative.Jeff Roughgarden, Ph.D., MCDBA, MCSDTue, 25 Mar 2008 10:21:20 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxNaturally I cannot remember where I read the articles I would like to reference but I did find the following:Academic sytle paperData Extraction and Ad Hoc Query of an Entity— Attribute— Value Databasehttp://www.pubmedcentral.nih.gov/articlerender.fcgi?artid=61332See section 11 Disccussion of drawackshttp://en.wikipedia.org/wiki/Entity-Attribute-Value_modelSee Downsideshttp://www.answers.com/topic/entity-attribute-value-model?cat=technologyArticle in favor of generic design with many mostly negative comments postedhttp://www.sswug.org/see/26210Posted 4 table model of everything with a few mostly negative and not overly specific disagreementshttp://discuss.joelonsoftware.com/default.asp?design.4.331499.15The basic EV design works great when you have only one set of attributes to values, it works pretty good when you have a few sets of attributes to value pairs by a limited number of groups. By add another layer or two of grouping and increase the volume then the model starts to break down as soon as your query complexity increases.If you are just considering using the model for a simple parameter table then you will probably not have to deal with most of the issues but if you needs are more complex you can quickly get into trouble.-- Mark D Powell --Tue, 25 Mar 2008 08:01:06 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspx[quote][b]tymberwyld (3/24/2008)[/b][hr]I love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.[/quote]I agree... event logging and audit logs are a great example of EAV's. I also use them with great success in cross-tab reporting... first step is frequently to put the data into some form of EAV as a derived table. If I really want things to fly, the EAV will be a temp table with the correct index. I've made several monthly reports that report 9 different aggregated attributes by hour of day by day of month for all days of the month. Millions of rows are the source of the data (IVR application, in this case) and they are quickly aggregated into an EAV and then cross-tabbed with ease. And, unlike the Pivot function, can return more than one measure (if you want to call it that.).We've also very successfully used them in "Customer Added 'Fields'"... not something you should go nuts with, but effective when the customer uses them properly.As has been said, they have their place and that's not "everywhere".Mon, 24 Mar 2008 20:17:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxCade,True - but not in this case. They actually used a TEXT field for the 'value' part......MarkMon, 24 Mar 2008 19:26:55 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.Mon, 24 Mar 2008 19:18:18 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI can see the flexibility demands that a rapidly changing data model requires. But I will argue strongly against abstracting the data model away from the database layer.All these conceptual thought games around changing data models, software changes and end user configurablity just pass the decisions in a changing application to a person that is abstracted from a relational view of the performance impact of the data model change they are contemplating.Unmanaged incremental performance risk with potentially cheaper development blind to the risks of the modelling decisions they are making. If only I could force the decision makers to do the ongoing support for these applications.Mon, 24 Mar 2008 16:40:50 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspx[quote][b]JRoughgarden (3/24/2008)[/b][hr]Mark,Could you give some links to discussion on the (lack of) scalability of the EAV model? Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?Thanks,Jeff[/quote]I will try to address the implementation specific of EAV in my part II article and hopefully it will answer all your concerns.regardsTeshomeMon, 24 Mar 2008 15:49:43 GMTTeshome-283915http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxMark,Could you give some links to discussion on the (lack of) scalability of the EAV model? Also, what do you recommend in its place, master entity tables with 1-1 subtables for entity subclasses?Thanks,JeffMon, 24 Mar 2008 15:46:14 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxThere is a good deal of information on the web explaining why the EV model does not scale and does not perform well under load.No amount of indexing will fix the basic flaw in the EV design.Some people are fooled by the relative decent performance for single row and related row set retrieval but it is very easy to find fairly simple queries that do not run well with this design. Once you get 250 million rows of EV values like an application we purchased has you can pretty much schedule vacation while you wait for results.-- Mark D Powell --Mon, 24 Mar 2008 12:54:10 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI would definitely push this way down in a wrapper, so the app was a shielded as possible from the raw EAV. Less room for developers to make mistakes in their reading and writing.Mon, 24 Mar 2008 12:01:29 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxYes, the C# code has to use the culture-aware parse method, and is aware of the requested culture.Int32..::.Parse Method (String, NumberStyles, IFormatProvider)Mon, 24 Mar 2008 11:22:13 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxSince you are dealing with internationalization already, what form are you using for numeric data in the fields?:9,999.999.999,99Your C# code will need to parse with a specific locale, otherwise users changing their regional settings will result in differing formats.This is a fundamental flaw in EAV models - interpretation of what is, effectively, free-form data that the database cannot help you enforce the rules on.Even when it is only used for configuration, if the value is used as a threshold option like "Minimum Shipping Charge", it could come into calculations.EAV is useful - it's great for options and configurations.Not so good for the "designing a database within a database" idea.But this problem of type-safety and validation has not yet been addressed to my satisfaction in any architecture I've seen.Mon, 24 Mar 2008 10:58:25 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI am currently designing a DB that employs this name value pair approach to store arbitrary sets of attributes to be associated to a variety of entities. I am concerned about performance. but generally deal with with performance problems after they arise. The product and marketing folks love the concept because it lets them promise anything to customers. They can avoid writing specs and get flexibility at the expense of performance, which is not their problem anyway. :rolleyes:One alternative seems to be entity tables with an ever-increasing list of nullable fields associated with the entities of interest, the union of all attributes. I'm not very keen on this approach either. Another approach is to have a master entity table with 1-1 subordinate sub-tables for the various entity types, but this also makes for a complicated schema.I am setting the data type for the attribute values to be nvarchar, for localization. Three-character prefixes on the attribute names indicate the actual type of the value, e.g. strName, datEffective, smnPrice, etc. C# programmers are comfortable with the .Parse(string) functions and can retrieve the strongly typed actual value with these methods.I hope this all works out, but any words of warning are much appreciated.Jeff Roughgarden, MCDBA, MCSDMon, 24 Mar 2008 10:40:11 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxSQL Server 2008 has better support for EAV by sparse columns/columns setMon, 24 Mar 2008 09:09:13 GMTpeterhehttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxEAV has its place. Regarding performance, indexes and efficient code can usually mitigate that.I'd like to see more discussion about type-safety and whether there are any tricks for that which I haven't seen.Mon, 24 Mar 2008 06:52:09 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxPart I is a good (possibly too brief) introduction to EAV's ;) Like the other's I'm looking forward to Part II.Mon, 24 Mar 2008 06:03:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxHi mark,I am also looking forward to comments on part II of the article. ThanksTeshomeMon, 24 Mar 2008 05:40:57 GMTTeshome-283915http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxLooking forward to part 2.I inherited a DB like this - using name value pairs. Biggest drawback: Performance!Mon, 24 Mar 2008 05:27:16 GMTSuperDBA-207096http://www.sqlservercentral.com/Forums/Topic473346-1235-1.aspxI am nearly there and will try to get in as soon as I can.regardsTeshomeMon, 24 Mar 2008 05:19:49 GMTTeshome-283915