Options for handling a frequently changing data form

Question

What are some possible designs to deal with frequently changing data forms?

I have a basic CRUD web application where the main data entry form changes yearly. So each record should be tied to a specific version of the form. This requirement is kind of new, so the existing application was not built with this in mind.

I'm looking for different ways of handling this, hoping to avoid future technical debt. Here are some options I've come up with:

• Create a new object, UI and set of tables for each version. This is obviously the most naive approach.
• Keep adding all the fields to the same object and DB tables, but show/hide them based on the form version. This will become a mess after a few changes.
• Build form definitions, then dynamically build the UI and store the data as some dictionary like format (e.g. JSON/XML or maybe an document oriented database) I think this is going to be too complex for the scope of this app, especially for the UI.

What other possibilities are there? Does anyone have experience doing this? I'm looking for some design patterns to help deal with the complexity.

Answer 1

First, I will speak to your solutions above and then I will give my answer.

• Creating a new table for each version is going to require new programming every year since you will not be able to dynamically join to the new table and include the new columns easily. That seems pretty obvious and really makes this a bad choice.
• The issues you mentioned with adding the columns to the same form are
  correct. Also, whatever database you are using has a max on how many columns it can handle and how many bytes it can have in a row. That could become another concern.
• The third option I think is the closest to what you want. I would not store the new column data in a JSON/XML unless it is for duplication to increase speed. I think this is your best option
• The only option you didn't mention was storing all of the data in 1 database field and using XML to parse. This option would make it tough to query and write reports against.

If I had to do this:

1. The first table would have the columns ID (seeded), Name, InputType, CreateDate, ExpirationDate, and CssClass. I would call it tbInputs.
2. The second table would have the have 5 columns, ID, Input_ID (with FK to tbInputs.ID), Entry_ID (with FK to the main/original table) value, and CreateDate. The FK to the main/original table would allow you to find what items were attached to what form entry. I would call this table tbInputValues.
3. If you don't plan on having that base table then I would use a simply table that tracks the creation date, creator ID, and the form_id.
4. Once you have those you will just need to create a dynamic form that pulls back all of the inputs that are currently active and display them. I would put all of the dynamic controls inside of some kind of container like a <div> since it will allow you to loop through them without knowing the name of every element. Then insert into tbInputValues the ID of the input and its value.
5. Create a form to add or remove an input. This would mean you would not have much if any maintenance work to do each year.

I think this solution may not seem like the most eloquent but if executed correctly I do think it is your most flexible solution that requires the least amount of technical debt.

Answer 2

I think the third approach (XML) is the most flexible. A simple XML structure is generated very fast and can be easily versioned and validated against an XSD.

You'd have a table holding the XML in one column and the year/version this xml applies to.

Generating UI code based on the schema is basically a bad idea. If you do not require extensive validation, you can opt for a simple editable table.

If you need a custom form every year, I'd look at it as kind of a job guarantee :-) It's important to make the versioning mechanism and extension transparent and explicit though.

Answer 3

For this particular app, we decided to deal with the problem as if there was one form that continuously grows. Due to the nature of the form this seemed more natural than more explicit separation. We will have a mapping of year->field for parts of the application that do need to know which data is for which year.

For the UI, we will be creating a new page for each year's form. Dynamic form creation is far too complex in this situation.