I have an application that supports user defined fields for my customer table, and I already have something like this:
CustomFields table
Id Name DataType
--------------------------------------------
1 Hobbies 1
2 Number of Siblings 3
The above table shows a table that contains the UDF that are used application wide. The values in the DataType column translates to something like this:
1: string
2: DateTime
3: Integer
The following table consists of the values for the UDFs
CustomFieldsValues table
CustomerId FieldId StringValue NumericValue DateValue
1234 1 Fishing NULL NULL
1234 2 NULL 6 NULL
8834 1 Golfing NULL NULL
8834 2 NULL 2 NULL
Now, I would like to introduce a new "DataType"
4: DropDownList
Which is essentially like the string
data type, except that, instead of rendering a textbox, I would have a dropdownlist instead, with values that will be added in by the administrator.
What I can think of at the moment is to have another table
FieldDropDownList table
FieldId DropDownItem
1 Fishing
1 Golf
1 Swimming
2 Random value #1
2 Random value #2
3 Random value #3
And all custom fields with data type 4, will have their values saved in the StringValue
column of the CustomFieldsValues
table
Are there any suggestions, and considerations I should make?
What would be the most efficient way in implementing a dropdownlist UDF?