Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

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

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

  • Comments 3
  • Likes

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

Comments
  • UPDATED 20 Dec 2008 to fix links It’s that time of year again, when I disappear from the blogosphere

  • How then would you report on those xml blobs?

    You can query those...can  you?

  • @Bernie: yes, you can use XQuery to access data within the XML, and primary and secondary XML indexes to optimize the performance of that access.

    Check out the XML tag on this blog for more info, or post another question!

        -wp

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment