0

I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem.

User Table:

  • userID
  • firstName
  • lastName
  • phone
  • email

Form Table currently contains:

  • userID
  • question1
  • question2
  • question3

I'm struggling with the fact that, due to the marketing nature of this tool, the form structure and fields are almost guaranteed to change sometime in the future. There will likely be fields added/subtracted in the future. Any changes in the future are completely unknown now but could become something to the extent of:

Form Table could change to:

  • userID
  • question1
  • question3
  • questionA
  • questionB

Where question2 was removed and two completely new fields were added.

Should each "version" of the form have its own table or just add columns to the existing table as the form changes? Are there better ways to handle this?

4
  • 1
    You should add more details about the current and future structure of this DB
    – McNets
    Commented Feb 7, 2020 at 14:45
  • @McNets Thanks! I've added some details to illustrate the fields.
    – Vecta
    Commented Feb 7, 2020 at 14:52
  • You can read about Column-oriented databases or vertical databases. Commented Feb 10, 2020 at 16:42
  • Learn how to use ALTER TABLE ... ADD COLUMN ..., but be aware that it can be costly.
    – Rick James
    Commented Feb 12, 2020 at 5:05

1 Answer 1

0

My approach would be to make the table with a VARCHAR(MAX) field for the questions, and another field for the answers, and just dump each into those fields. That table can have date and version information too to make things easier. Everything should be structured within the varchar field, so it can be parsed out later.

A second approach that a co-worker of mine took was to have a table of just questions, and then a table for the form that links the questions to a certain form. Then have a table for responses, which would only link a person's response to a question.

So the forms table would have a series of records that link form 127 to questions 5,3,24,58. The response table would link person 626's responses to those questions, with a date. The worst part of it would be having to make cross joins just to see a single person's responses to a single form, but that's what views were made for.

3
  • Use TEXT instead of VARCHAR(MAX).
    – Rick James
    Commented Feb 12, 2020 at 5:04
  • Sorry, I missed the mysql tag, and I even forgot to add that I'm exclusively MSSQL Server, so I'll add the caveat that any datatypes, terms, or conventions mentioned should be adjusted for your environment Commented Feb 12, 2020 at 13:56
  • Some "sql" advice is universal; some is too vendor-specific. Yes, please add caveats.
    – Rick James
    Commented Feb 12, 2020 at 23:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.