We are looking at giving our clients the ability to create User Defined Field in our application. Is there any sample/guidance that I could obtain from StrataFrame community?

D 

Most database I have seen will either tack on X number of fields to a table (user 1, user1, etc) or have a child table with the same, this is the easiest method you will find in my opinion.

The downside is that you are limiting yourself and have to pick a number of fields and live with them, adding more down the road requires modifications to the database.

We recently took a different approach and implemented a solution where the user defined fields are stored in a table vertically (one row per field) and then pivoted with the TSQL PIVOT command into place. This allows an unlimted number of user defined fields with no modifications to the database later. The controls that display these user fields are generated dynamically on our forms in a flow layout panel.

We also tried storing the user fields in an XML column but had problems with performance with hundreds of thousands of records, if you volume is low it might be a good alternative.

It really depends on your needs and hopefully this gives some food for thought.

There are a lot of good topic on the net about this topic, it will in the end really boil down to your needs, there is in my opinion no one single absolutely right answer.

I would stay away from dynamically altering your table structures, you will find that suggestion out there and I think it is the only one I would not seriously consider.

Good luck!

Keith Chisarik

I have an application that requires user defined fields.  In the original version I used the method suggested by Keith of stacking them vertically one field per row and pivoting later. This seemed to be the best solution as it had the advantage of not requiring a database change to increase the number of user defined fields. What I found was there was a lot of work in managing these fields both at the business object level and the UI.

In the end I found that in practical use the number of user defined fields never exceeded 10 so in version 2.0  I switched to just allocating 10 user defined fields in my main table along with adding 10 fields to the UI where I can hide the unused fields through a combination of visibility and form resizing.  All of the fields are stored as string and I control what the user can enter into the field by creating a field definition record for each user defined field that holds an input mask, RegEx string, base data type, and other information to allow enforcement of business rules and conversion to other data types

As Keith said it really depends on what your needs are. The ‘stacking’ solution is more elegant and allows greater flexibility but the pre-allocated method is far easier to implement.

-Larry

Yes it definitely adds a layer of complexity, but the business requirement was "unlimited flexibility, no need to change the code/database down the road"

Tks for all your suggestion. I will try the PIVOT option.