An Answer For Spazecaze: A Reasonable Method To Implement User Defined Fields

Spazecaze discovered a February, 2006 discussion of order of operations and poses the following question in its comment area:

So how would you go about designing a database that allows for end user defined fields?

For instance, a system is built to allow users to enter data into fields on a form. These fields are defined by a different user as being either date, int or string. So you could have x number of y type fields on a form.

How would you store each data type in a different typed column and retrieve the correct value from the correct column?

It is analogous to designing a database to store metadata.

Well, Spazecaze, the first thing I would do is drill deeper into the requirement. Is there really such a high need for flexibility, or do we perhaps have more data entry forms than we thought we did? How are we going to report on this data?

If we can avoid this sort of implementation, we'll all certainly be better off.

Assuming that the requirement is in fact legitimate, I'd instantiate this data in a self-describing XML document, XML is perfect for this sort of requirement, as completely disparate data can be stored within the same column (and within the same XML structure, if it's properly designed). XML indexes can then be exploited to tune any reports which you might run against this data.

There are of course a great many subtleties to discuss here. Please let me know if you'd like to pursue the issue further, and thanks for your question!

     -wp