Re: Re-design of data storage table
Date: Wed, 25 Feb 2009 09:28:59 -0800 (PST)
On Feb 23, 5:31 pm, Erland Sommarskog <esq..._at_sommarskog.se> wrote:
> __Paul (paulwragg2..._at_hotmail.com) writes:
> > First off, thanks to everybody for their replies. As I mentioned
> > earlier, I had already come up with a method of doing this but did not
> > want to influence peoples replies. As it turns out it looks like I
> > have come up with a modified version of EAV. I say modifed because
> > rather than storing each attribute/value item in a single table I have
> > a different table for each data type. Anyway, some responses below:
> Not sure that I see the point with a different table per type.
> However, you may find the data type sql_variant interesting, at least
> if you don't care about being portable. This type permits you to store
> the user-defined attributes in a single column, and you can still enforce
> the data type, so that you don't store a string for an attribute that
> is a data.
> > Our domain is clinical data - the database is for a product and not
> > for an in-house system. Therefore it is imperative that users can add
> > their own attributes without having to modify the database schema
> > itself. I would guess this does qualify as a special case.
> Special case and special case, it's not uncommon to see questions on
> the newsgroup asking on how to implement customer-defined attributes.
> And there is not really any good solution, but you have to pick the
> one than that is the least evil in your case.
> Erland Sommarskog, SQL Server MVP, esq..._at_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
Avoid the EAV model like the plague that it truly is.
Easy for java developers because it makes easy SQL for inserts. Easy for java developers because there's no hassle of datatype checking, (everything is a varchar2(100);
Impossible for reporting due to the 7-8 self-joins just to get 1 user's name, and address with zipcode and phone.
Easy for java developers to blame the oracle DBA as he/she doesn't
know or won't tell us the magic hidden parameter that will instantly
speed queries up.
Easy for PMs to blame the oracle DBA as this application is fast as hell on Joey Java's laptop and he has lots of rows in his database, thousands of them even. So why doesn't it work on millions. Must be an Oracle issue.
Been there, done that.
"Run like hell !"
Roger Gorden Received on Wed Feb 25 2009 - 11:28:59 CST