My requirements are:
- Need to be able to dynamically add User-Defined fields of any data type
- Need to be able to query UDF fields quickly
- Need to be able to do calculations on UDF fields based on datatype
- Need to be able to sort UDF fields based on datatype
- 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 field is only attached to a few thousand of the Master records, not all of them
- UDF fields are not joined or used as keys. They're just data used for queries or reports
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.
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.
Create a single table containing UDFFieldName, UDFDataType, and Value. When a new UDFField 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.
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
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.
If performance is the primary concern, I would go with #6... a table per UDF (really, this is a variant of #2). This answer is specifically tailored to this situation and the description of the data distribution and access patterns described.
Because you indicate that some UDFs have values for a small portion of the overall data set, a separate table would give you the best performance because that table will be only as large as it needs to be to support the UDF. The same holds true for the related indices.
You also get a speed boost by limiting the amount of data that has to be processed for aggregations or other transformations. Splitting the data out into multiple tables lets you perform some of the aggregating and other statistical analysis on the UDF data, then join that result to the master table via foreign key to get the non-aggregated attributes.
You can use table/column names that reflect what the data actually is.
You have complete control to use data types, check constraints, default values, etc. to define the data domains. Don't underestimate the performance hit resulting from on-the-fly data type conversion. Such constraints also help RDBMS query optimizers develop more effective plans.
Should you ever need to use foreign keys, built-in declarative referential integrity is rarely out-performed by trigger-based or application level constraint enforcement.
This could create a lot of tables. Enforcing schema separation and/or a naming convention would alleviate this.
There is more application code needed to operate the UDF definition and management. I expect this is still less code needed than for the original options 1, 3, & 4.
If there is anything about the nature of the data that would make sense for the UDFs to be grouped, that should be encouraged. That way, those data elements can be combined into a single table. For example, let's say you have UDFs for color, size, and cost. The tendency in the data is that most instances of this data looks like
'red', 'large', 45.03
NULL, 'medium', NULL
In such a case, you won't incur a noticeable speed penalty by combining the 3 columns in 1 table because few values would be NULL and you avoid making 2 more tables, which is 2 fewer joins needed when you need to access all 3 columns.
If you hit a performance wall from a UDF that is heavily populated and frequently used, then that should be considered for inclusion in the master table.
Logical table design can take you to a certain point, but when the record counts get truly massive, you also should start looking at what table partitioning options are provided by your RDBMS of choice.