Others

How to Design a Normalized Database Structure for a Multiple-Choice Questionnaire

Khadija Khan Oct 19, 2026 868 views

Question details

The user needs to design a normalized relational database to manage a 50-question multiple-choice quiz, including tracking respondents, recording selected answers, and calculating scores.

How to Design a Normalized Database Structure for a Multiple-Choice Questionnaire

Product: Microsoft Access
Device & OS: not provided
Scenario: Setting up a database to administer, track, and score multiple-choice tests with four options per question.
Observed behavior: The user wants a correct, normalized multi-table structure instead of a flat-file design to efficiently store questions, valid options, and user submissions.

Before you start

Before building your questionnaire database, ensure you are familiar with primary keys, foreign keys, and basic table normalization concepts to correctly establish relationships between your data entities.

Solution 1Recommended

Implement a Normalized Multi-Table Structure

Use distinct, related tables for questions, possible answers, and respondents to ensure your database is normalized, scalable, and easy to query.

A common mistake is storing possible answers or user responses directly within the questions table. A normalized four-entity model prevents data duplication and provides the flexibility to alter the number of questions or choices at any time.

Create the Questions Table

Set up a table named tblQuestions with a primary key QuestionID (AutoNumber), a text field for QuestionText, and an optional Points field if questions have varying weights.

Create the Possible Answers Table

Build tblPossibleAnswers with PossibleAnswerID (primary key), a foreign key QuestionID linked to your questions table, AnswerText, and a boolean IsCorrect field.

Set Up the Respondents Table

Define tblRespondents to store user information, utilizing a RespondentID primary key along with standard name and demographic fields.

Track Quiz Attempts

Create tblQuizAttempts using an AttemptID primary key, linking it to RespondentID as a foreign key, and include an AttemptDate field to track when the quiz was taken.

Record the Submissions

Set up tblResponses to track individual user answers. Use ResponseID as the primary key, alongside foreign keys for AttemptID, QuestionID, and PossibleAnswerID.

Implement a Normalized Multi-Table Structure

Solution 2

Calculate Scores and Enforce Submission Rules

Utilize built-in queries to calculate grades dynamically based on the stored correct answers, rather than hardcoding scores directly into the response tables.

Free Microsoft Office alternative

Manage and Analyze Survey Data with WPS Office

If designing a complex relational database in Microsoft Access feels overwhelming, you can easily manage, distribute, and analyze your multiple-choice questionnaires using WPS Spreadsheet. WPS Office offers a lightweight, highly compatible alternative to Microsoft Office for tracking daily data.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet to create clear, flat-file trackers or open exported survey data.
3. Analyze Survey Data: Utilize built-in PivotTables and functions like COUNTIF to quickly tabulate multiple-choice responses without complex SQL queries.

Fully compatible with Microsoft Excel formats (.xlsx, .csv) for seamless data sharing.Quickly analyze questionnaire responses using PivotTables and built-in formulas.Lightweight suite that installs quickly without slowing down your computer.Free to use with a familiar, user-friendly interface requiring zero learning curve.

Frequently Asked Questions

Can I store all responses as separate columns in the respondents table?

It is highly discouraged. Storing responses as columns (e.g., Answer1, Answer2) violates database normalization rules, making it extremely difficult to query data, calculate scores, or expand the quiz later without redesigning the entire table.

How do I handle questions that have different numbers of choices?

By using a separate tblPossibleAnswers table linked to the QuestionID, you can add as many or as few option records as needed for each question. The structure remains intact whether a question has two choices or ten.

Where should the score for each individual question be stored?

Instead of storing a static score, it is best practice to calculate it dynamically using a query that counts responses linked to a tblPossibleAnswers record where IsCorrect is True. If point values vary by question, store the specific point value in tblQuestions.

Office Quick Solutions