Design patterns for user defined fields with dropdownlist support

Question

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?

Answer 1

Are there any suggestions, and considerations I should make?

Yes. Start over, and let the DBMS do the work! That DataType column is a warning bell that something is wrong. The DBMS provides types, type safety, and type conversion.

Separate your UDFs into CustomIntFields, CustomStrFields, and CustomDateFields. If desired laster, you can represent them as a single view, using a UNION:

create view CustomFields as 
select 's' as type, FieldID, Name from CustomStrFields UNION
select 'i' as type, FieldID, Name from CustomIntFields UNION
select 'd' as type, FieldID, Name from CustomDateFields;

Just for starters, that will let the DBMS ensure on your behalf that dates have dates and integers have numbers.

The DropDowns table becomes

create table DropDowns
  ( DropDownID int  -- indicating the widget
  , type char(1) 
  , FieldID int
  );

referencing the union of the the three UDF tables.

This design lets fields be added without automatically appearing in the dropdown, which might not be what you want. If every field is supposed to appear in only one particular dropdown, the dropdown ID could be added to the three field tables and everything driven from the view.

What would be the most efficient way

This stuff is all very static and small. I have a hard time believing efficiency will be an issue. But I do think programmer and customer satisfaction will be higher by using the DBMS in the way it was intended. :-)