2

We are using SQL Server 2008 and one of the requirements is to have extendable user defined attributes on the entities that are defined for the system. For example, we might have a entity called Doctor, we want the admins of the system to be able to define extra attributes that normally are not in the system. These attributes will most likely be needed as query criteria linking parent or joiner tables.

There will be tables that define the attributes (Name, description, type) and so forth, but my question is on the storage of the actual data values.

Im not a DBA (just a programmer pretending to be one) but my first thought was to store them in one generic column as a 

nvarchar(450)

This would cover most of the basic types and still allow an index, but I thought I would run into lots of conversion type issues (converting to dates, numbers, etc.) as well as unusual query issues since everything is a nvarchar.

So, my latest thinking is to create a column for each data type that we would support:

ColNVarCharData
nvarchar(450) 

ColBitData
bit

ColIntData
int

..And so forth

When the user defined the extendable attribute, they would pick a data type and then we would store attribute value in that column for that type. For example, if they picked int, the data value would be stored ColIntData and the other two columns would be null in this example.

I think this solves the conversion issues rather than storing each attribute as a generic type. Additionally I could add indexes as needed for each type depending on the query's being used.

I am leaning towards using this, but wondered if anyone else had any suggestions. I have breifly looked at the XML data type, but the "schema" could be changing quite frequently, so I thought this was a better fit.

Jon Raynor
  • 3,804
  • 6
  • 29
  • 43
  • You will have additional overhead with this method since you will have to lookup the datatype from your reference table, as well as the attribute ID – JNK Jul 20 '11 at 20:36
  • This is an "EAV" proposal. See http://en.wikipedia.org/wiki/Entity-attribute-value_model for alternatives. It's normally a SQL anti-pattern. Given your client base, surely you can capture most of the model up front... – gbn Jul 20 '11 at 20:41
  • @gbn , thanks for the link, it was good read. Yes, we can capture most of the model upfront. This would be for the outliers or new data points added after the system was in place to help reduce the need for maintenance efforts. – Jon Raynor Jul 20 '11 at 21:00
  • @Jon Raynor: personally, I'd take the maintenance hit. If you plan ahead, you should be able to cater for it by *knwoing* what you need to change to add new columns quickly. Do you want a CSV in an EAV because they really need a new 1:n child table? And searchable too... Now define "maintenance effort" :-) – gbn Jul 20 '11 at 21:02

2 Answers2

2

Here are just a few SO questions/answers relevant to the topic.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
-1

Based on the link the @gbn sent me in the comments, I think this is an effective answer (Taken from the WIKI link):

The Value

Coercing all values into strings, as in the EAV data example above, results in a simple, but non-scalable, structure: constant data type inter-conversions are required if one wants to do anything with the values, and an index on the value column of an EAV table is essentially useless. Also, it is not convenient to store large binary data, such as images, in Base64 encoded form in the same table as small integers or strings. Therefore larger systems use separate EAV tables for each data type (including binary large objects, "BLOBS"), with the metadata for a given attribute identifying the EAV table in which its data will be stored. This approach is actually quite efficient because the modest amount of attribute metadata for a given class or form that a user chooses to work with can be cached readily in memory. However, it requires moving of data from one table to another if an attribute’s data type is changed. (This does not happen often, but mistakes can be made in metadata definition just as in database schema design.)

Jon Raynor
  • 3,804
  • 6
  • 29
  • 43