sql - Extendable Database Schema - How to store the extendable attribute values -

we using sql server 2008 , 1 of requirements have extendable user defined attributes on entities defined system. example, might have entity called doctor, want admins of system able define attributes not in system. these attributes needed query criteria linking parent or joiner tables.

there tables define attributes (name, description, type) , forth, question on storage of actual data values.

im not dba (just programmer pretending one) first thought store them in 1 generic column

nvarchar(450) 

this cover of basic types , still allow index, thought run lots of conversion type issues (converting dates, numbers, etc.) unusual query issues since nvarchar.

so, latest thinking create column each data type support:

colnvarchardata nvarchar(450)   colbitdata bit  colintdata int 

..and forth

when user defined extendable attribute, pick data type , store attribute value in column type. example, if picked int, data value stored colintdata , other 2 columns null in example.

i think solves conversion issues rather storing each attribute generic type. additionally add indexes needed each type depending on query's being used.

i leaning towards using this, wondered if else had suggestions. have breifly looked @ xml data type, "schema" changing quite frequently, thought better fit.

here few questions/answers relevant topic.

• one
• two
• three