Best ways to allow users to dynamically add columns to a table?

Smirk posted on Thursday, October 19, 2006

My table has certain preset columns that ship with the product.  I want my end user to have the ability to add/remove some user-defined columns at runtime.

Obviously this presents a problem for any stored procedures or SQL statements that I may have in my DataPortal_XYZ() methods, because they assume knowledge of the table structure.

Any best practice advice on how to handle this sort of usage?

Thanks

Mark Haley

 

Brian Criswell replied on Thursday, October 19, 2006

I like to create attribute tables.  You have one table that contains the attributes/custom fields that are available.  This would hold the type of attribute, what object it corresponded to, etc.  Another table would hold the custom attributes for instances of objects.  It would hold the attribute it corresponds to and the instance of an object that it corresponds to as well as the attribute's value.  Each object that can have custom attributes would have a list of objects that correspond to the second table I described.

figuerres replied on Thursday, October 19, 2006

Brian Criswell:

I like to create attribute tables.  You have one table that contains the attributes/custom fields that are available.  This would hold the type of attribute, what object it corresponded to, etc.  Another table would hold the custom attributes for instances of objects.  It would hold the attribute it corresponds to and the instance of an object that it corresponds to as well as the attribute's value.  Each object that can have custom attributes would have a list of objects that correspond to the second table I described.

Agreed!

any app that starts letting users modify the table structure is begging for all kinds of problems.

it's fine to add optional fileds like memo, 3-4-nth phone,email, etc...

or to add a set of user-defined data tables with some logic on how to add and edit them.

just a few things if users edit tables:

how do you upgrade the app later when you do not know the table structure?

and what happens if the user creates a new column and your update wants to use that name for a column?

also if users add a bunch of nvarchar(MAX) to a table it could get way big and slow down the system.

and what if you have a bug in the code you use to alter a table ?? bad support call if the bug just droped a table or nuked the sql db!

just some ideas on why not modify tables that way.

Smirk replied on Thursday, October 19, 2006

Thanks for the replies, guys!

- Brian,  I'm trying to work out what you said, but I'm afraid I need a little bit more detail to wrap my head around it.  Any further comment you could give would be great.

- figuerres, thanks for your comments and I agree with them.  I had already put some thought into maintaining safety in this process. 

For instance, any column a user would add would be prefixed with User_ etc.  Columns that I need would not be modifyable by the user.

Thanks

Mark Haley

Brian Criswell replied on Friday, October 20, 2006

Possibly the best way to describe this would be to give you an example.  Download and install the latest version of Gemini, a free (for up to ten users) incident management tool.  Look at how it deals with custom fields both in the user interface and in the database.
http://www.countersoft.com/

Smirk replied on Friday, October 20, 2006

Bayu - WOW!

Thanks for the detailed explanation of your project and it's way of working with this issue.  I certainly appreciate the time that it took you to write all that you did!

As you suggested, I pulled out a note pad and diagrammed the table structure as you explained it and I'll be working through the implementation details as it applies to my project.

I especially liked what you said: 

"The rule of thumb I learned long ago about SQL is that all dynamics should (must) go in vertical direction, i.e.: your records. You never, I repeat _never_, allow dynamics in horizontal direction, i.e. addition or removal of columns."

Sounds like a good axiom to me!  (which is also what figuerres was saying...)

Brian,

Thanks again for your imput.  I'm currently downloading Gemini and will see what I can discover there...

It's interresting: I was re-watching Rocky's DNRTV episodes yesterday, and he commented about how honored he was with the user community that he has, and I certainly have to agree with him.  You guys are quite involved and helpful.

Thanks again!

Mark Haley

Brian Criswell replied on Friday, October 20, 2006

Hey, no problem.  Glad to help.  I think you will find that all of us were saying about the same thing, just from different viewpoints.  The Gemini example is a fairly good implementation of custom fields which I believe you will be able to easily adapt, and it follows what we were saying about using a separate table/dynamics in a vertical direction.

Bayu replied on Friday, October 20, 2006

Glad to be of help. :-)

I'm downloading this Gemini too, sounds interesting.

Bayu

ChristianPena replied on Friday, October 20, 2006

Smirk,

We have a related requirement here at work; The grids we have our read-only info in should be able to add and remove columns to meet the users needs. We are looking into the approach I have described in the post below. With this approach, we need only to change the source query to change what is displayed.

http://forums.lhotka.net/forums/thread/7745.aspx

I don't see why the approach wouldn't carry over to an editable object, but have not looked too far into it.

Good luck.

Christian

Bayu replied on Friday, October 20, 2006

Hi all,

(edit: maybe have pen and paper ready and draw some stuff while reading this ...?)

For my last project I have been implementing something which I believe is exactly what you would need. I believe it corresponds to what Brian outlines, but since you asked for more details let me elaborate on what I have done.

The challenge was:
- the app dealt with 'products'
- some of the 'aspects' of the product were known a priori
- and the user had to be enabled to configure any additional 'custom aspect' that would he would like
- to make it slightly more challenging: there were of course different 'product types', and so the custom aspects had to be specific for a particular 'product type'

The rule of thumb I learned long ago about SQL is that all dynamics should (must) go in vertical direction, i.e.: your records. You never, I repeat _never_, allow dynamics in horizontal direction, i.e. addition or removal of columns. Someone already pointed out the issues you will face when you need to upgrade stuff.

So, when building this concept bottom-to-top:
- first I created two tables to hold all the actual product data: 'product' and 'aspect' where 'aspect has a foreign key to 'product'
- then I also had two typedefinition tables: 'producttype' and 'aspecttype', and 'product' and 'aspect' are altered to have foreign keys to the respective type-table. Note that 'aspecttype' does NOT have a foreign key relationship with producttype, this is arranged in the subsequent tables.
- the last layer that I put on top could be seen as a configuration layer, which basically describes which product type maps to which aspect types. This involves two tables: 'productconfig' and 'aspectconfig', and here aspectconfig has a foreign key to productconfig. Basically these tables define the allowed combinations of products and aspects.

From a user-interface perspective this follows pretty much what Brian already mentioned:
- master-detail view: one grid for products and another for the aspects
- in the product editor the ui is constructed dynamically, based on the aspects defined for the given product type
- I even implemented it such that you can change the product type (as opposed to preselecting it) and the editor will change accordingly (hiding/showing aspects)
- I also augmented my aspecttype definition with datatype info so that the product editor knows whether to display a textbox (for strings), checkbox (for booleans) or numberinput-only textbox

The definition of producttypes, aspecttypes and their allowed configurations is done in a different view which is only accessible by 'admin'-users. (which would be just me. ;-) ).

It's a lot of administration on the background, but this way everything is neatly normalized in the database.Additionally, the relational model offers maximum flexibility towards user-specific product configurations. And last but not least, every product and aspect is associated with a type-id, which allows other modules of the application (like financing or inventory) to relate on different levels of abstraction (specific product, aspecttype level or producttype level). This in turn allows my reporting module to extract any kind of statistics desired.


After having implemented the described datamodel I have extended it one level further:
- the customer wanted to be able to restrict the set of allowed values for each 'aspect'
- from a ui-perspective this implied that all textboxes had to be replaced by comboboxes

The implementation was straightforward:
- added a 'aspectvalue' table to hold all known values, it has a foreign key to 'aspecttype'
- I altered the 'aspect' table (the one holding the actual data), the 'value' column was replaced with a 'value-id' column that points to the newly added aspectvalue table
- added a 'aspectvalueconfig' table with a foreign key to 'aspectconfig' and 'aspectvalue', the purpose of this table is to define the allowed values for a particular producttype-aspecttype combination (so, the allowed values per aspect could differ depending on the producttype that it is configured for)
- and having all this in place, I naturally added a 'defaultvalue-id' column to 'aspectconfig' so that  I know which values to display in the comboboxes for newly inserted products.

How it works:
- the product editor now still shows all configured aspects for the given producttype
- the value for every aspect can now be chosen using a combobox, where each combobox is filled according to the allowed aspectvalues as configured in 'aspectvalueconfig' and has the most commonly used value preselected (using the defaultvalue-id on aspectconfig).

From a the perspective of other modules another level of detail is added since every aspectvalue is now associated with a unique id. Also more detailed statistics for the purpose of reporting can be extracted. In fact, with my Artificial Intelligence background I know of a huge number of analysis tools that would be interesting to investigate. :-)

It's become quite a long explanation. Sorry if I overload you with too many details. However, this product editor has turned out to be the key selling point of our software, so I know it's worth the pain of going through. :-)

Regards,
Bayu

Pavel replied on Thursday, May 03, 2007

Bayu,

It's nice implementation, but how you deal with Data types such as Boolean or Date?