StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, I will speak to your solutions above and then I will give my answer.</p> <ul> <li>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.</li> <li>The issues you mentioned with adding the columns to the same form are<br> 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.</li> <li>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</li> <li>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.</li> </ul> <p>If I had to do this:</p> <ol> <li>The first table would have the columns ID (seeded), Name, InputType, CreateDate, ExpirationDate, and CssClass. I would call it tbInputs.</li> <li>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. </li> <li>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.</li> <li>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 <code><div></code> 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.</li> <li>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.</li> </ol> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload