数据库表关联引用类型设计及考试题库试卷表关系咨询

算力阿峰

2026-5-29

Answers to Your Database Questions

1. Storing a List of References to Other Tables

In relational databases like MySQL, you shouldn't store a list of foreign keys as a comma-separated string or similar—this violates database normalization, makes queries (like filtering questions by course) slow and messy, and leads to data inconsistency.

Instead, use a many-to-many junction table to represent the relationship between two entities. For example, if a question needs to link to multiple courses, create a separate table that maps each question to its associated courses. This keeps your data clean, enforces referential integrity, and makes it easy to query/update relationships.

2. Fixing & Optimizing the Exam Question Bank & Paper Database

Let's go through your existing schema, fix its issues, and adjust it to meet all your requirements (including per-question scoring in exams).

First, Fix Existing Table Issues

Your current tables have a few typos and missing relationships:

question table: subjetID is misspelled (should be subjectID), and questionNum. has an invalid dot in the column name (rename to questionNumber).
exam table: pointsPerQuestionl is misspelled (and we'll remove this anyway, since we need per-question scoring).
course table: Missing a link to subject—since each course belongs to one subject, add a subjectID foreign key here.

Revised & Complete Schema

Here's the corrected schema with all required relationships and tables:

1. Subject Table (Clarified)

CREATE TABLE `subject` (
  `subjectID` varchar(2) NOT NULL,
  `subjectName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`subjectID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Stores core subjects (e.g., 02 = Math).

2. Course Table (Added Subject Link)

CREATE TABLE `course` (
  `courseID` varchar(2) NOT NULL,
  `subjectID` varchar(2) NOT NULL,
  `courseName` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`courseID`),
  CONSTRAINT `course_subject_fk` FOREIGN KEY (`subjectID`) 
    REFERENCES `subject` (`subjectID`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Each course is tied to one subject (e.g., 03 = Algebra, linked to subject 02).

3. Question Table (Fixed Typos & Added Required Field)

CREATE TABLE `question` (
  `subjectID` varchar(2) NOT NULL,
  `questionNumber` varchar(3) NOT NULL,
  `questionText` varchar(100) DEFAULT NULL,
  `answer1` varchar(100) DEFAULT NULL,
  `answer2` varchar(100) DEFAULT NULL,
  `answer3` varchar(100) DEFAULT NULL,
  `answer4` varchar(100) DEFAULT NULL,
  `correctAnswer` varchar(1) NOT NULL, -- Stores which option is correct (e.g., '1' or 'A')
  PRIMARY KEY (`subjectID`, `questionNumber`),
  CONSTRAINT `question_subject_fk` FOREIGN KEY (`subjectID`) 
    REFERENCES `subject` (`subjectID`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The composite primary key (subjectID + questionNumber) forms your 5-digit unique question ID (e.g., 02 + 105 = 02105).
Added correctAnswer to fulfill your requirement of storing the right option.

4. Question-Course Junction Table (For Multi-Course Links)

Since a question can be linked to multiple courses in the same subject, use this table to avoid storing a list of course IDs:

CREATE TABLE `question_course` (
  `subjectID` varchar(2) NOT NULL,
  `questionNumber` varchar(3) NOT NULL,
  `courseID` varchar(2) NOT NULL,
  PRIMARY KEY (`subjectID`, `questionNumber`, `courseID`),
  CONSTRAINT `qc_question_fk` FOREIGN KEY (`subjectID`, `questionNumber`) 
    REFERENCES `question` (`subjectID`, `questionNumber`) 
    ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `qc_course_fk` FOREIGN KEY (`courseID`) 
    REFERENCES `course` (`courseID`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This maps each question to its associated courses cleanly.

5. Exam Table (Fixed & Simplified)

CREATE TABLE `exam` (
  `subjectID` varchar(2) NOT NULL,
  `courseID` varchar(2) NOT NULL,
  `examNumber` varchar(2) NOT NULL,
  `duration` int(11) DEFAULT NULL, -- In minutes, e.g., 90 for 1.5 hours
  PRIMARY KEY (`subjectID`, `courseID`, `examNumber`),
  CONSTRAINT `exam_course_fk` FOREIGN KEY (`courseID`) 
    REFERENCES `course` (`courseID`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `exam_subject_fk` FOREIGN KEY (`subjectID`) 
    REFERENCES `subject` (`subjectID`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The composite primary key (subjectID + courseID + examNumber) forms your 6-digit unique exam ID (e.g., 02 + 03 + 01 = 020301).
Removed the global pointsPerQuestion field—we'll handle per-question scoring in the next table.

6. Exam-Question Junction Table (For Per-Question Scoring)

This table links exams to questions and stores the specific score for each question in that exam:

CREATE TABLE `exam_question` (
  `subjectID` varchar(2) NOT NULL,
  `courseID` varchar(2) NOT NULL,
  `examNumber` varchar(2) NOT NULL,
  `questionSubjectID` varchar(2) NOT NULL,
  `questionNumber` varchar(3) NOT NULL,
  `score` int(11) NOT NULL, -- Custom score for this question in the exam
  PRIMARY KEY (`subjectID`, `courseID`, `examNumber`, `questionSubjectID`, `questionNumber`),
  CONSTRAINT `eq_exam_fk` FOREIGN KEY (`subjectID`, `courseID`, `examNumber`) 
    REFERENCES `exam` (`subjectID`, `courseID`, `examNumber`) 
    ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `eq_question_fk` FOREIGN KEY (`questionSubjectID`, `questionNumber`) 
    REFERENCES `question` (`subjectID`, `questionNumber`) 
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Each row represents one question added to an exam, with its own unique score.
ON DELETE CASCADE ensures that if an exam is deleted, all its linked questions are removed too (adjust this if you need different behavior).

How This Meets Your Requirements

Question Bank: Teachers can create questions tied to one subject and multiple courses (via question_course). Each question has a unique 5-digit ID from the composite primary key.
Paper Building: Teachers create exams tied to a subject and course, with a unique 6-digit ID. Adding questions to an exam via exam_question lets you set a custom score for each question.
Referential Integrity: All foreign keys ensure you can't add a question to an exam that doesn't exist, or a course to a question that's in a different subject.

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