匹配题型（Match the following）数据库设计方案求助

Hey there! Great question—match-the-following questions do require a bit of careful modeling since they involve paired items, but we can build a scalable, clean design that fits right in with your existing setup. Here's a proven approach I've used in similar assessment systems:

1. Extend Your Existing questions Table #

First, make sure your existing questions table has a field to identify the question type—something like question_type (an ENUM that includes your existing types: ESSAY, SHORT_ANSWER, MULTIPLE_CHOICE, plus the new MATCHING). This table will hold shared fields like the main question text, points value, associated exam, etc., keeping consistency with your current setup.

2. Optional: matching_sides Table (For Custom Column Labels) #

If you want to let authors define custom labels for the left/right columns (e.g., "Terms" vs. "Definitions" or "Country" vs. "Capital"), add this table to store those labels per matching question:

CREATE TABLE matching_sides (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    left_label VARCHAR(255) NOT NULL,
    right_label VARCHAR(255) NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

If all matching questions use default labels (like "Left Items" / "Right Items"), you can skip this table and hardcode the labels in your frontend instead.

3. matching_items Table (Store All Matchable Entries) #

This table holds every individual item that appears in either the left or right column of a matching question. It supports any number of items per question:

CREATE TABLE matching_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    side ENUM('LEFT', 'RIGHT') NOT NULL, -- Marks if this is a left or right column item
    content TEXT NOT NULL, -- The actual text of the item (e.g., "Photosynthesis" or "Converts light to chemical energy")
    sort_order INT NOT NULL, -- Controls the display order of items (so you can arrange them in a specific sequence)
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
);

4. matching_correct_pairs Table (Store Valid Matches) #

This table defines which left items correspond to which right items—critical for grading:

CREATE TABLE matching_correct_pairs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    question_id INT NOT NULL,
    left_item_id INT NOT NULL,
    right_item_id INT NOT NULL,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    FOREIGN KEY (left_item_id) REFERENCES matching_items(id) ON DELETE CASCADE,
    FOREIGN KEY (right_item_id) REFERENCES matching_items(id) ON DELETE CASCADE,
    UNIQUE KEY unique_pair (left_item_id, right_item_id) -- Prevents duplicate pairings for the same question
);

Note: You’ll want to enforce that left_item_id references a matching_items record where side='LEFT' (and same for right items) either via database triggers or your application logic.

Let’s say you have a matching question like:

Match the biological process with its description:
Left Column: A. Photosynthesis, B. Cellular Respiration
Right Column: 1. Uses oxygen to produce energy, 2. Converts light energy to chemical energy

Here’s how it would look in the database:

1. questions Table: A single record with question_type='MATCHING', content='Match the biological process with its description:'
2. matching_sides Table: A record linked to the question, with left_label='Biological Process', right_label='Description'
3. matching_items Table:
   • Record 1: question_id=X, side='LEFT', content='A. Photosynthesis', sort_order=1
   • Record 2: question_id=X, side='LEFT', content='B. Cellular Respiration', sort_order=2
   • Record 3: question_id=X, side='RIGHT', content='1. Uses oxygen to produce energy', sort_order=1
   • Record 4: question_id=X, side='RIGHT', content='2. Converts light energy to chemical energy', sort_order=2
4. matching_correct_pairs Table:
   • Record 1: question_id=X, left_item_id=1, right_item_id=4
   • Record 2: question_id=X, left_item_id=2, right_item_id=3

• Seamless Integration: Fits directly with your existing question table structure—no major refactoring needed
• Full Flexibility: Supports any number of left/right items per question (no arbitrary limits)
• Clear Grading Logic: The pairing table makes it easy to validate user answers and calculate scores
• Controlled Display: The sort_order field ensures items appear in the exact order the author intends

If you ever need to support more complex matching (like one-to-many or many-to-one pairs), you can simply adjust the unique constraints on matching_correct_pairs—but this design covers the vast majority of standard matching question use cases.

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