Quiz系统user_quiz表关联与数据库规范化问题咨询

Hey there! Let's work through your Quiz system database design challenge together—this is a common spot to get stuck, so let's break it down clearly.

Using userid as the sole primary key for user_quiz means each user can only have one record here. That works if you only need to store a running total of completed quizzes and total score, but it's not great for tracking individual quiz attempts (like when a user finished a specific quiz, or their score on that exact quiz).

For better normalization and future flexibility, I'd split this into two parts:

• A detailed attempt table to log every quiz a user completes
• A summary table (or view) to hold the aggregated totals you need

Assuming you already have a users table (with primary key userid) and a quizzes table (with primary key quizid), here are two solid approaches:

Option 1: Track Every Individual Attempt (High Normalization) #

This is the most scalable approach, as it preserves all historical data. Create a user_quiz_attempts table:

CREATE TABLE user_quiz_attempts (
    attempt_id INT AUTO_INCREMENT PRIMARY KEY,
    userid INT NOT NULL,
    quizid INT NOT NULL,
    score INT NOT NULL,
    completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Foreign keys to link to core tables
    FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE,
    FOREIGN KEY (quizid) REFERENCES quizzes(quizid) ON DELETE CASCADE,
    -- Optional: Prevent users from retaking the same quiz (remove if retakes are allowed)
    UNIQUE KEY (userid, quizid)
);

To get the total completed quizzes and score for a user, just run an aggregate query:

SELECT 
    userid,
    COUNT(quizid) AS completed_quiz_count,
    SUM(score) AS total_score
FROM user_quiz_attempts
GROUP BY userid;

If you need to pull these stats frequently, turn that query into a view so you can access it like a table:

CREATE VIEW user_quiz_stats AS
SELECT 
    userid,
    COUNT(quizid) AS completed_quiz_count,
    SUM(score) AS total_score
FROM user_quiz_attempts
GROUP BY userid;

Option 2: Store Only Aggregated Totals (Simple Use Cases) #

If you don't need to track individual attempts and just want the running totals, you can keep your original user_quiz table—just make sure the foreign key is set up correctly:

CREATE TABLE user_quiz (
    userid INT PRIMARY KEY,
    completed_quiz_count INT NOT NULL DEFAULT 0,
    total_score INT NOT NULL DEFAULT 0,
    FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE
);

In this case, every time a user finishes a quiz, you'll run an UPDATE query to increment the count and add the new score instead of inserting a new record.

• 1NF (Atomic Values): Never store multiple quiz scores or IDs in a single field. Each record should represent one attempt (or one user's totals, in the simple case).
• 2NF (Eliminate Partial Dependencies): If you use a composite primary key (like userid + quizid in the attempt table), make sure all other fields (like score) depend on the entire key, not just part of it.
• 3NF (Eliminate Transitive Dependencies): Don't store redundant data—for example, don't put the quiz name in user_quiz_attempts; pull it from the quizzes table using the quizid foreign key instead.

• If you want to allow retakes, remove the UNIQUE KEY (userid, quizid) constraint from the attempt table.
• Add an is_passed BOOLEAN field to the attempt table if you need to track which quizzes a user passed.
• For more complex quizzes (like multiple questions), add a user_quiz_answers table linked to attempt_id to log each question's answer and whether it was correct.

内容的提问来源于stack exchange，提问作者Zestyy99