Extendable Database Schema - How to store the exte

问题:

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.

回答1:

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

• One
• Two
• Three

回答2:

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.)