Options for Processing Frequently Changing Data Forms

Question

What are the options for handling frequently changing data forms?

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

I am looking for various ways to handle this, hoping to avoid future technical debt. Here are some options I came up with:

• Create a new object, user interface and a set of tables for each version. This is by far the most naive approach.
• Continue adding all the fields to the same DB object and tables, but show / hide them based on the version of the form. After a few changes, this will become a mess.
• Define the forms of the forms, then dynamically create the user interface and save the data in the form of some dictionary, such as a format (for example, JSON / XML or, possibly, a document-oriented database). I think it will be too difficult for the scope of this application, especially for the user interface.

What other options are there? Does anyone have any experience with this? I am looking for some design patterns to help deal with complexity.

Answer 1

For this particular application, we decided to solve the problem as if there was one form that is constantly growing. Due to the nature of the form, this seemed more natural than a clearer separation. We will display the year-> field for parts of the application that need to know what data for which year.

For the user interface, we will create a new page for each form of the year. Creating a dynamic form in this situation is too complicated.

Answer 2

First, I will talk to your decisions above, and then I will give my answer.

• Creating a new table for each version will require new programming every year, since you will not be able to dynamically join a new table and include new columns easily. This seems pretty obvious and really makes this a bad choice.
• You mentioned problems adding columns in the same form: true. Also, no matter what database max is used, how many columns it can handle and how many bytes it can have in a row. This could be another problem.
• The third option, I think, is closest to what you want. I would not store the new column data in JSON / XML unless it is for duplication to increase speed. I think this is your best option.
• The only option that you did not mention is storing all the data in 1 database and using XML to parse. This option will make it difficult to request and write reports against.

If I had to do this:

• The first table will have the column IDs (seeded), Name, InputType, CreateDate, ExpirationDate, and CssClass. I will call it tbInputs.
• The second table will have 5 columns, identifier, Input_ID (from FK to tbInputs.ID), Entry_ID (from FK to main / original table) and CreateDate. FK to the main / original table will allow you to find which items were attached to which form of record. I would call it the tbInputValues ​​table.
• If you do not plan to have this base table then I would just use a table that keeps track of the creation date, creator ID, and form_id.
• After that, you just need to create a dynamic form that discards all the inputs that are currently active and display them. I would put all the dynamic controls inside some kind of container like a <div> , as it will allow you to scroll through them without knowing the name of each element. Then insert the login ID and its value into tbInputValues.
• Create a form to add or remove input. This would mean that you do not have much if any kind of service work every year.

I think that this decision may not seem the most eloquent, but if it is implemented correctly, I think that this is your most flexible solution, requiring the least amount of technical debt.

Answer 3

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

You will have a table with XML in one column and the year / version this xml belongs to.

Generating schema-based user interface code is basically a bad idea. If you do not need a thorough check, you can choose a simple editable table.

If you need a custom form every year, I would look at it as a guarantee of work :-) It is important to make the mechanism and version extension transparent and explicit, though.