Re: Re-design of data storage table

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

__Paul ( 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,

Links for SQL Server Books Online:
SQL 2008:
SQL 2005:
SQL 2000:
Received on Mon Feb 23 2009 - 16:31:51 CST

Original text of this message