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