How to design a database for User Defined Fields?

Question

My requirements are:

• Need to be able to dynamically add User-Defined fields of any data type
• Need to be able to query UDFs quickly
• Need to be able to do calculations on UDFs based on datatype
• Need to be able to sort UDFs based on datatype

Other Information:

• I'm looking for performance primarily
• There are a few million Master records which can have UDF data attached
• When I last checked, there were over 50mil UDF records in our current database
• Most of the time, a UDF is only attached to a few thousand of the Master records, not all of them
• UDFs are not joined or used as keys. They're just data used for queries or reports

Options:

1. Create a big table with StringValue1, StringValue2... IntValue1, IntValue2,... etc. I hate this idea, but will consider it if someone can tell me it is better than other ideas and why.

2. Create a dynamic table which adds a new column on demand as needed. I also don't like this idea since I feel performance would be slow unless you indexed every column.

3. Create a single table containing UDFName, UDFDataType, and Value. When a new UDF gets added, generate a View which pulls just that data and parses it into whatever type is specified. Items which don't meet the parsing criteria return NULL.

4. Create multiple UDF tables, one per data type. So we'd have tables for UDFStrings, UDFDates, etc. Probably would do the same as #2 and auto-generate a View anytime a new field gets added

5. XML DataTypes? I haven't worked with these before but have seen them mentioned. Not sure if they'd give me the results I want, especially with performance.

6. Something else?

Answer 1

I would most probably create a table of the following structure:

• varchar Name
• varchar Type
• decimal NumberValue
• varchar StringValue
• date DateValue

The exact types of course depend on your needs (and of course on the dbms you are using). You could also use the NumberValue (decimal) field for int's and booleans. You may need other types as well.

You need some link to the Master records which own the value. It's probably easiest and fastest to create a user fields table for each master table and add a simple foreign key. This way you can filter master records by user fields easily and quickly.

You may want to have some kind of meta data information. So you end up with the following:

Table UdfMetaData

• int id
• varchar Name
• varchar Type

Table MasterUdfValues

• int Master_FK
• int MetaData_FK
• decimal NumberValue
• varchar StringValue
• date DateValue

---

Whatever you do, I would not change the table structure dynamically. It is a maintenance nightmare. I would also not use XML structures, they are much too slow.