Technical Wording help (not code)

[blacksaibot]

I need help putting this into technical terminology:

Our database "admin team" designed database to house survey questions. The problem is they gave every single question its own column and every single answer its own column. Essential an entire survey for one person is stored in a single data row.

I've seen survey databases before and each row represents a question and its response. I remember learning its better to have many rows rather than too many columns. I also know that the way they set this up is bad database design practice, but I don't know the technical terminology to describe this.

Can anyone help me out? Thanks

[June7]

It is called 'data normalization'. Google it, lots of info.

[blacksaibot]

It is called 'data normalization'. Google it, lots of info.

Okay... but the table is not redundant at all. It just shoves too many things into one record. So I'm not sure how normalization is really the answer.

[June7]

It is not fully normalized because you have multiple similar name fields. If you had to add another question it would mean another field. Tables have a limit of 255 fields. If you have more than 255 questions will run out of fields. Often see this similar name field issue with a structure that has fields for month/year. Eventually will run out of fields. Full normalization is not always required, depends on your needs. It is a balancing act between normalization and ease of data entry/output. This structure might be quite adequate for you.

[Dal Jeanis]

One of the real-world reasons that you don't want to store the survey all on one record, is that every time you want to change a question's wording, add a question, or delete a question, then you have to create a new table for the new kind of survey. on the other hand, if you normalize the questions onto a table of their own, then all you have to do is enter a new question into the question table, and record the answer for that question normally.


tblPerson
PersonID

tblSurvey (record for a person took a survey)
SurveyID (autokey for this survey taken by this person)
PersonID (who took it)
SurveyDate (when they took it)

tblQuestion
QuestionKey (autokey)
QuestionWording
AnswerType (fill in, numeric 1-5, yes/no or whatever)

tblSurveyAnswers
SurveyID (foreign key to which survey record)
QuestionID
Answer



It could be made more complicated than that, for instance by having a "Survey Template" table that stores the list of questions that were sent, but that gives you the general idea of how the normalized structure should be for a Survey Database, if you want to be able to analyze the answers easily.