Re: Re-design of data storage table

From: Erland Sommarskog <esquel_at_sommarskog.se>
Date: Mon, 23 Feb 2009 22:31:51 +0000 (UTC)
Message-ID: <Xns9BBBF2FA28B55Yazorman_at_127.0.0.1>



__Paul (paulwragg2323_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, esquel_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
Received on Mon Feb 23 2009 - 16:31:51 CST

Original text of this message