Hello,



I am currently facing a design where a table (virtually) needs to store
attributes of a topic (related table). The attributes can be user defined,
i.e. not known at development type and depend on other factors. Each
attributes value can be one of different types (int, int64, double, string)
and may have constraints like min, max or length (string).



Thus the data type would be modeled as variant in some programming
languages, but this is not an option in SQL (beside the MS SQLserver
sql_variant extension).



So, how to simulate this in SQL.



Basically there would be one table describing the attributes type (Type
identifier, min/max,.) and one table for the values itself.



The design I am currently thinking of would be to make exactly these two
tables, with the attributes having a Dataype column and iMin,iMax for
integer, i64Min, i64Max, fMin,fMax for double , sMinLen, sMaxLen for
strings. The same applies to the value table which will have iValue,
i64Value, fValue, string columns to hold the actual data.



Now the columns will be filled according to the data type, columns not
matching the type will be NULL.



This means that each row in the table will have virtual space for any data
type which violates database normalization. However it seems to me to be the
best deal for performance and data space as NULL takes virtually no room
(4/8 bytes in total for a number of fields in some circumstances) and
requires no complex queries.



One could also imagine to have the constraints moved to a separate table and
interpreted according to the data type. Also a table for each type could be
imagined but this will make the queries very complicated working against
performance.



Note: The model has to work for huge databases



Anybody has a better alternative ?