Dynamic Tables vs EAV

>> Indeed, splitting the code-base is yet another option. It's more like dengue-fever. <<

LOL! I was thinking multiple personality disorders ..
 >> Stay informed about: Dynamic Tables vs EAV 

I see the usual suspects show up and throw rocks, and offer no solutions - because they simply don't have any.

How would you suggest implementing a product configurator? Shall I add a "fabric_color" column to my FeatureDescription table, to go along with "boat_motor"?

Looking forward to your response (and solution), Usual Suspects.

Oh wait, I already know the solution: Create completely separate codebases and models for every different possible domain that comes along. Maintain 1000 different database schemas. Good one.

> On Saturday, August 09, 2008 8:28 AM Erland Sommarskog wrote:

> ( ) writes:
>
> A third alternative is to have a set of columns uservalue1, uservalue2 etc,
> to which properties can be tied to dynamically.
>
> There should be tons of material out there on Google that discusses these
> different approaches. And there is certainly no shortage of damnation of
> EAV. And, sure, EAV is very problematic. But the other two approaches are as
> well.
>
> And which is the best for you, we cannot answer, because we don't know
> your area of business and how these dynamic properties will be used. What
> I can say is that I have used EAV in a few corners in the application I work
> with, and I'm very satisfied with the result. But these corners are very
> narrowly confined. If you expect to have write queries that goes "show me
> entities that have property X > 3, that have no instance of property Y,
> and at least to Z", then EAV is going to lead you into misery.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>> On Saturday, August 09, 2008 10:17 AM TomaszBorawsk wrote:

>> I would not decide to choose dynamic tables because:
>> - number of column and size of single row is limited.
>> - when you add new column to the table you should recompile all procedures
>> that use it.
>> - I can find any pros for this solution.
>>
>> The second soluton give you also ability to create you aplication language
>> independent (languageid column in property value table)
>> There is one con: you have to define maximum property size (texts).
>>
>>
>> " " wrote:


>>> On Saturday, August 09, 2008 6:22 PM Erland Sommarskog wrote:

>>> ( ) writes:
>>>
>>> If you got performance problem with an XML solution (which also is a
>>> possible way to go), I think the risk is considerable what you will
>>> get the performance problems with EAV as well. That is, I assume that
>>> you will run that type of queries where EAV gets really painful.
>>>
>>> ALTER TABLE at run-time is certainly not pleasant, and if there are
>>> things like index views that need to be rebuilt, there is all reason to
>>> restrict this possibility so that it only can be performed by a super-
>>> user, who is instructed to such things on off-hours.
>>>
>>> But I like to stress that it is very difficult to say with certainty,
>>> when I don't know your application, so you will need to use your gut
>>> feeling.
>>>
>>>
>>> --
>>> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>>>
>>> Links for SQL Server Books Online:
>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>>>> On Saturday, August 09, 2008 9:01 PM Michael Coles wrote:

>>>> FYI, SQL 2008 has XML column sets which provide similar functionality to
>>>> what you previously implemented, although it is integrated in T-SQL so you
>>>> can essentially query and manipulate the nodes of the XML column sets using
>>>> normal SELECT and DML statements.
>>>>
>>>> BTW, how did you model your XML solution? Are you using attributes as keys
>>>> or actual node names as keys? And is your XML indexed with a primary and
>>>> secondary XML indexes? If you don't have XML indexes on your key-value pair
>>>> XML column you'll notice some expensive TVF steps in the query plan. These
>>>> are the "shredding" operations where SQL Server is dynamically turning your
>>>> XML data into relational format on the fly as you query. If you create
>>>> proper primary and secondary XML indexes on your column you will notice the
>>>> TVF shredding steps will be replaced with much more efficient seek
>>>> operations. Shredding is generally the most expensive XML operation, and
>>>> elimating this at query time can improve performance significantly.
>>>>
>>>> --
>>>>
>>>> ========
>>>> Michael Coles
>>>> "Pro T-SQL 2008 Programmer's Guide"
>>>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
>>>>
>>>> wrote in message
>>>>
>>>> Erland,
>>>>
>>>> Thank you for your reply.
>>>>
>>>> Originally we implemented our model using a xml column in the entity
>>>> containing key-value pair elements. However, we had problems with the
>>>> performance of certain operations, specially the ones where we had to
>>>> use a mix of XQuery and TSQL.
>>>>
>>>> In our domain, the property set for each entity depends on the
>>>> business rules for the specific deployment. The number of common
>>>> properties is very small and modelled in the entity table. Users can
>>>> also define additional properties at run-time. These properties are
>>>> also typed, which posed some problems. The functionality is exposed
>>>> via services that untimatelly use SPs in the database to perform the
>>>> operations in the entities.
>>>>
>>>> The reason that I thought of an approach that alters the table was
>>>> performance. Hoever, I noticed some drawbacks: for instance, all the
>>>> views for that table need to be recreated and all the corresponding
>>>> indexes for the view rebuilt, in case they are materialized. I could
>>>> mitigate this by requiring creation of properties to be reserved to
>>>> highly priviledges. Could you comment on this?
>>>>
>>>> Kind regards
>>>> CD
>>>>
>>>> On Aug 9, 5:28 am, Erland Sommarskog wrote:


>>>>> On Sunday, August 10, 2008 12:56 AM Michael Coles wrote:

>>>>> OK I remember that conversation now, thanks for jogging my memory. Since
>>>>> you have primary and secondary XML indexes on the table how were the query
>>>>> plans affected? Are you certain that the XQueries are the cause of the poor
>>>>> performance (for example, if you eliminate the XQueries are there any
>>>>> poorly-performing joins or other operations)? Can you post/attach some
>>>>> sample XML query plans of the poorly performing queries? What type of
>>>>> secondary XML index are you using?
>>>>>
>>>>> --
>>>>>
>>>>> ========
>>>>> Michael Coles
>>>>> "Pro T-SQL 2008 Programmer's Guide"
>>>>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
>>>>>
>>>>>
>>>>> wrote in message
>>>>>
>>>>> Hello Michael,
>>>>>
>>>>>
>>>>> I will check on the xml colum sets feature of SQL Server 2008.
>>>>>
>>>>> I posted about problems with my xml solution in the past. Please refer
>>>>> to the thread below:
>>>>>
>>>>> http://groups.google.com/group/microsoft.public.sqlserver.programming/...wse_thr
>>>>>
>>>>> In my design, some entities have a xml column like:
>>>>> <Top>
>>>>> <Props>
>>>>> <Prop>
>>>>> <N> Prop1 </N>
>>>>> <V> Value1 </V>
>>>>> <Prop>
>>>>> <Prop>
>>>>> <N> Prop2 </N>
>>>>> <V> Value2 </V>
>>>>> <Prop>
>>>>> <Prop>
>>>>> <N> Prop3 </N>
>>>>> <V> Value3 </V>
>>>>> <Prop>
>>>>> </Props>
>>>>> </Top>
>>>>>
>>>>> Each column is validated against the schema and I have the primary and
>>>>> some secondary xml indexes. However, since my value elements are of
>>>>> type "anytype" is appears that the index is not helping much. In
>>>>> addition, performing queries using XQuery is just horrible (please
>>>>> refer to the problem I posted in the referred thread above). This is
>>>>> the reason I am thinking about redesigning the model.
>>>>>
>>>>> If you have any suggestion, I would really appreciate.
>>>>>
>>>>> Kind regards
>>>>> CD
>>>>>
>>>>>
>>>>> On Aug 9, 6:01 pm, "Michael Coles"
>>>>> <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote:


>>>>>> On Sunday, August 10, 2008 3:31 AM Tibor Karaszi wrote:

>>>>>> 2005: 1024.
>>>>>> 2008: 1024 regular columns and 29000 sparse columns.
>>>>>>
>>>>>> --
>>>>>> Tibor Karaszi, SQL Server MVP
>>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>>>
>>>>>>
>>>>>> wrote in message
>>>>>>
>>>>>> Hello Tomasz,
>>>>>>
>>>>>> What is the limit on the number of columns in SQL 2005? or 2008?
>>>>>>
>>>>>> Thanks
>>>>>> CD
>>>>>>
>>>>>> On Aug 9, 7:17 am, Tomasz Borawski
>>>>>> wrote:


>>>>>>> On Sunday, August 10, 2008 2:04 PM Michael Coles wrote:

>>>>>>> <snip>But it is cancer versus AIDS.</snip>
>>>>>>>
>>>>>>> That comparison is in really, really poor taste. A bad database design
>>>>>>> might cost you a job, but it's not going to destroy your body and take your
>>>>>>> life no matter how poorly it's designed.
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> ========
>>>>>>> Michael Coles
>>>>>>> "Pro T-SQL 2008 Programmer's Guide"
>>>>>>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X


>>>>>>>> On Sunday, August 10, 2008 5:54 PM Erland Sommarskog wrote:

>>>>>>>> Michael Coles (michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net) writes:
>>>>>>>>
>>>>>>>> Funny. To me that looked like a modernised version of an expression we
>>>>>>>> have in Swedish, "choose between the plague and cholera", which exactly
>>>>>>>> catpures what Joe was trying to say. I don't know if English has anything
>>>>>>>> close to this, or if Joe just made this up as he typed.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>>>>>>>>
>>>>>>>> Links for SQL Server Books Online:
>>>>>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>>>>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>>>>>>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>>>>>>>>> On Sunday, August 10, 2008 9:58 PM Michael Coles wrote:

>>>>>>>>> "Erland Sommarskog" wrote in message
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I can appreciate the sentiment that bad database design is ... well ... bad.
>>>>>>>>> Spend some time volunteering in the cancer ward of the local children's
>>>>>>>>> hospital and then decide for yourself if bad database design is equivalent
>>>>>>>>> to a slow and painful death from a combination of cancer and radiation
>>>>>>>>> treatment.
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>>
>>>>>>>>> ========
>>>>>>>>> Michael Coles
>>>>>>>>> "Pro T-SQL 2008 Programmer's Guide"
>>>>>>>>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X


>>>>>>>>>> On Monday, August 11, 2008 2:17 AM steve dassin wrote:

>>>>>>>>>> Are you really a nitwit if no one says you are? Unfortunately this forum
>>>>>>>>>> doesn't have the number of nitwit police it once had. Participation is
>>>>>>>>>> generally limited to the usual suspects, there are far to few unusual ones.
>>>>>>>>>> So now you face little in the way of public humiliation and embarrassment
>>>>>>>>>> for the stupid and ridiculous. As the saying goes, there never seems to be a
>>>>>>>>>> cop when you need one:(
>>>>>>>>>>
>>>>>>>>>> www.beyondsql.blogspot.com
>>>>>>>>>>
>>>>>>>>>> "Michael Coles" <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote in
>>>>>>>>>> message


>>>>>>>>>>> On Monday, August 11, 2008 5:29 PM Erland Sommarskog wrote:

>>>>>>>>>>> --CELKO-- (jcelko212@earthlink.net) writes:
>>>>>>>>>>>
>>>>>>>>>>> Indeed, splitting the code-base is yet another option. It's more like
>>>>>>>>>>> dengue-fever.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se
>>>>>>>>>>>
>>>>>>>>>>> Links for SQL Server Books Online:
>>>>>>>>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>>>>>>>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>>>>>>>>>> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


>>>>>>>>>>>> On Wednesday, August 13, 2008 7:50 AM crbd9 wrote:

>>>>>>>>>>>> Hello All,
>>>>>>>>>>>>
>>>>>>>>>>>> The nature of the the business we target requires that some entities
>>>>>>>>>>>> in our model to have properties created at deployment and runtime.
>>>>>>>>>>>>
>>>>>>>>>>>> I would appreciate if you could comment on the pros and cons of the
>>>>>>>>>>>> following approaches:
>>>>>>>>>>>>
>>>>>>>>>>>> 1. Dynamic table: The table is dynamically altered (ALTER TABLE) when
>>>>>>>>>>>> new properties are added.
>>>>>>>>>>>> 2. EAV: Separate table storing the properties.
>>>>>>>>>>>>
>>>>>>>>>>>> Thank you
>>>>>>>>>>>> Cassiano


>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:50 AM crbd9 wrote:

>>>>>>>>>>>>> Erland,
>>>>>>>>>>>>>
>>>>>>>>>>>>> Thank you for your reply.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Originally we implemented our model using a xml column in the entity
>>>>>>>>>>>>> containing key-value pair elements. However, we had problems with the
>>>>>>>>>>>>> performance of certain operations, specially the ones where we had to
>>>>>>>>>>>>> use a mix of XQuery and TSQL.
>>>>>>>>>>>>>
>>>>>>>>>>>>> In our domain, the property set for each entity depends on the
>>>>>>>>>>>>> business rules for the specific deployment. The number of common
>>>>>>>>>>>>> properties is very small and modelled in the entity table. Users can
>>>>>>>>>>>>> also define additional properties at run-time. These properties are
>>>>>>>>>>>>> also typed, which posed some problems. The functionality is exposed
>>>>>>>>>>>>> via services that untimatelly use SPs in the database to perform the
>>>>>>>>>>>>> operations in the entities.
>>>>>>>>>>>>>
>>>>>>>>>>>>> The reason that I thought of an approach that alters the table was
>>>>>>>>>>>>> performance. Hoever, I noticed some drawbacks: for instance, all the
>>>>>>>>>>>>> views for that table need to be recreated and all the corresponding
>>>>>>>>>>>>> indexes for the view rebuilt, in case they are materialized. I could
>>>>>>>>>>>>> mitigate this by requiring creation of properties to be reserved to
>>>>>>>>>>>>> highly priviledges. Could you comment on this?
>>>>>>>>>>>>>
>>>>>>>>>>>>> Kind regards
>>>>>>>>>>>>> CD
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Aug 9, 5:28=A0am, Erland Sommarskog wrote:
>>>>>>>>>>>>> c,
>>>>>>>>>>>>> as
>>>>>>>>>>>>> ork
>>>>>>>>>>>>> x


>>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:50 AM crbd9 wrote:

>>>>>>>>>>>>>> Hello Michael,
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I will check on the xml colum sets feature of SQL Server 2008.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> I posted about problems with my xml solution in the past. Please refer
>>>>>>>>>>>>>> to the thread below:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> http://groups.google.com/group/microsoft.public.sqlserver.programming/brows=
>>>>>>>>>>>>>> e_thread/thread/9e409465ab40ea8/a3ae77c4173142a2?hl=3Den&lnk=3Dst&q=3Dcrbd9=
>>>>>>>>>>>>>> 8+ML#a3ae77c4173142a2
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> In my design, some entities have a xml column like:
>>>>>>>>>>>>>> <Top>
>>>>>>>>>>>>>> <Props>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> <N> Prop1 </N>
>>>>>>>>>>>>>> <V> Value1 </V>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> <N> Prop2 </N>
>>>>>>>>>>>>>> <V> Value2 </V>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> <N> Prop3 </N>
>>>>>>>>>>>>>> <V> Value3 </V>
>>>>>>>>>>>>>> <Prop>
>>>>>>>>>>>>>> </Props>
>>>>>>>>>>>>>> </Top>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Each column is validated against the schema and I have the primary and
>>>>>>>>>>>>>> some secondary xml indexes. However, since my value elements are of
>>>>>>>>>>>>>> type "anytype" is appears that the index is not helping much. In
>>>>>>>>>>>>>> addition, performing queries using XQuery is just horrible (please
>>>>>>>>>>>>>> refer to the problem I posted in the referred thread above). This is
>>>>>>>>>>>>>> the reason I am thinking about redesigning the model.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> If you have any suggestion, I would really appreciate.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Kind regards
>>>>>>>>>>>>>> CD
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> On Aug 9, 6:01=A0pm, "Michael Coles"
>>>>>>>>>>>>>> <michaelcoREPLACE_THIS_WITH_AT_SIGNoptonline.net> wrote:
>>>>>>>>>>>>>> u
>>>>>>>>>>>>>> ng
>>>>>>>>>>>>>> keys
>>>>>>>>>>>>>> nd
>>>>>>>>>>>>>> pair
>>>>>>>>>>>>>> hese
>>>>>>>>>>>>>> ur
>>>>>>>>>>>>>> he
>>>>>>>>>>>>>> nd
>>>>>>>>>>>>>> ammer-rsquo-Guide/dp/143021001X
>>>>>>>>>>>>>> se
>>>>>>>>>>>>>> f
>>>>>>>>>>>>>> re
>>>>>>>>>>>>>> at
>>>>>>>>>>>>>> y
>>>>>>>>>>>>>> me
>>>>>>>>>>>>>> spx- Hide quoted text -


>>>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:50 AM crbd9 wrote:

>>>>>>>>>>>>>>> Hello Tomasz,
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> What is the limit on the number of columns in SQL 2005? or 2008?
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Thanks
>>>>>>>>>>>>>>> CD
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> s
>>>>>>>>>>>>>>> e


>>>>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:50 AM --CELKO-- wrote:

>>>>>>>>>>>>>>>> Based on my experience, do not do either dynamic tables or EAV. Of
>>>>>>>>>>>>>>>> the two design mistakes, EAV is the worst. But it is cancer versus
>>>>>>>>>>>>>>>> AIDS.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> As you become more successful, each new client installation will
>>>>>>>>>>>>>>>> mutate into a new product. You will need at least two programmers
>>>>>>>>>>>>>>>> devoted to each client, in case you lose one. You will quickly become
>>>>>>>>>>>>>>>> the custom programming shop for each client, doing all their ad hoc
>>>>>>>>>>>>>>>> reports; You will quickly become the custom training shop for each
>>>>>>>>>>>>>>>> client, since each installation will be a little different.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> There is a difference between a robust, flexible system and one that
>>>>>>>>>>>>>>>> is like watery oatmeal.


>>>>>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:51 AM --CELKO-- wrote:

>>>>>>>>>>>>>>>>> I have spent a lot of time in Sweden over the years. As much as I
>>>>>>>>>>>>>>>>> hate cold, I really miss being there for Christmas. The closest
>>>>>>>>>>>>>>>>> phrase in English I can think of is "Between a rock a hard place" but
>>>>>>>>>>>>>>>>> it does not have the same punch as "plague and cholera", so I
>>>>>>>>>>>>>>>>> modernize it.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> I am waiting to use "choose between Vista and OS/JCL" in the trade
>>>>>>>>>>>>>>>>> press or a newsgroup, but I don't know how many people remember OS/JCL
>>>>>>>>>>>>>>>>> and would get it.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> At any rate, let's get back to topic. My point is that your code base
>>>>>>>>>>>>>>>>> will drift and you wind up with too many almost-the-same products to
>>>>>>>>>>>>>>>>> support and maintain. Decades ago, the US patent office installed
>>>>>>>>>>>>>>>>> word processors with "boilerplate" paragraph templates. It seemed
>>>>>>>>>>>>>>>>> reasonable since most of their work was repetitive legal documents.
>>>>>>>>>>>>>>>>> But nobody locked down the files and their content drifted as each
>>>>>>>>>>>>>>>>> secretary modified a template for an immediate situation. When you
>>>>>>>>>>>>>>>>> moved to another machine, a template file with the same name had the
>>>>>>>>>>>>>>>>> opposite meaning, there were dozens of personal templates that had no
>>>>>>>>>>>>>>>>> documentation, etc. A real mess.


>>>>>>>>>>>>>>>>>> On Wednesday, August 13, 2008 7:52 AM --CELKO-- wrote:

>>>>>>>>>>>>>>>>>> LOL! I was thinking multiple personality disorders ..


>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>>>>>>> SQL Operations on a Text File with ADO.NET
>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/37ed9e1b-c5de-4c0b-afbe-d8...f9a6ecf
 >> Stay informed about: Dynamic Tables vs EAV