MySQL存储多数据类型问卷答案的最优实践与数据库设计咨询

Hey there! Let's walk through your questionnaire app database design problem step by step, since you're dealing with millions of answers and need top-notch performance for analytical queries like averaging ages or filtering users by hourly wage. First, let's break down why your three proposed approaches aren't ideal, then dive into the best alternative.

Why Your Current Options Fall Short #

1. EAV with All Answers Stored as Text #

This is the worst choice for your use case. Storing everything as text means you'll have to cast values to their correct types every time you run analytical queries (e.g., AVG(CAST(answer_value AS UNSIGNED)) for age). Casting kills index efficiency, and with millions of rows, these queries will crawl. You also lose database-level data validation, pushing all checks to your app layer which is error-prone.

2. EAV with Multiple Type-Specific Fields #

While this fixes the data type issue, EAV's core problem remains: your answer table will have millions of rows (one per answer), and analytical queries will require filtering out NULL values for irrelevant columns (e.g., WHERE answer_int IS NOT NULL AND question_id = 123). Indexes will be less effective, and aggregations will be slower compared to structured tables. This doesn't scale well for your performance goals.

3. Dynamic Columns for New Questions #

This is a production nightmare. Frequent schema changes (adding columns on the fly) will lock tables, disrupt existing operations, and lead to a bloated table with hundreds of NULL columns (since most users won't answer every question). Databases also have hard limits on the number of columns (e.g., MySQL InnoDB caps at 1017), so this approach hits a wall quickly. Your app code will also become a mess trying to handle dynamic columns.

The Best Approach: Metadata-Driven Vertical Partitioning #

Instead of forcing all answers into a single table (EAV or dynamic columns), we'll split storage by data type and use a metadata table to track question definitions. This keeps your schema stable, leverages native database types for performance, and supports dynamic question creation without schema changes.

Step 1: Question Metadata Table #

First, create a table to store all admin-defined questions and their rules. This acts as your source of truth for what questions exist, their data types, validation rules, and active status.

CREATE TABLE question_definitions (
    question_id INT PRIMARY KEY AUTO_INCREMENT,
    question_text VARCHAR(255) NOT NULL,
    data_type ENUM('varchar', 'integer', 'decimal', 'text') NOT NULL,
    max_length INT NULL, -- Only applicable for varchar types
    decimal_precision INT NULL, -- For decimal types (e.g., 10)
    decimal_scale INT NULL, -- For decimal types (e.g., 2 for currency)
    validation_rules JSON NULL, -- Store rules like {"min": 18, "max": 120} for age
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Admins create/edit/deactivate questions here—no schema changes needed.

Step 2: Type-Specific Answer Tables #

Create separate tables for each data type. This keeps your data structured, allows efficient indexing, and makes analytical queries fast.

Example: Integer Answers (Age, etc.)

CREATE TABLE answers_integer (
    answer_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL, -- Foreign key to your users table
    question_id INT NOT NULL,
    answer_value INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES question_definitions(question_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_question (user_id, question_id), -- Fast lookup for a user's answers to a question
    INDEX idx_question_value (question_id, answer_value) -- Fast filtering/aggregation for a question
);

Example: Decimal Answers (Hourly Wage, etc.)

CREATE TABLE answers_decimal (
    answer_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    question_id INT NOT NULL,
    answer_value DECIMAL(10,2) NOT NULL, -- Adjust precision/scale based on your needs
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (question_id) REFERENCES question_definitions(question_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_question (user_id, question_id),
    INDEX idx_question_value (question_id, answer_value)
);

Repeat this pattern for answers_varchar (names, etc.) and answers_text (self-descriptions). For the text table, skip indexing answer_value since text indexes are inefficient for large content.

Why This Works for Your Use Case #

• Blazing Fast Analytical Queries: Calculating average age is as simple as SELECT AVG(answer_value) FROM answers_integer WHERE question_id = 456—no casting, full index utilization. Filtering users with hourly wage > 30.52 uses SELECT user_id FROM answers_decimal WHERE question_id = 789 AND answer_value > 30.52, which runs in milliseconds even on millions of rows.
• No Schema Changes: Admins add questions to question_definitions, your app inserts answers into the correct type table. No table locks, no production disruptions.
• Supports Deactivated Questions: When a question is marked inactive, you can either filter it out in queries or archive its answers to historical tables (e.g., answers_integer_historical) to keep main tables lean.
• Data Integrity: Leverage database-level data types (e.g., INT for age prevents non-numeric values) and use the validation_rules JSON in your app to enforce custom rules (e.g., age >= 18).
• Scalability: Add new data types (e.g., date, boolean) by creating new answer tables—no impact on existing data.

Bonus Optimization Tips #

• Precompute Aggregates: For frequently run stats (e.g., daily average age), use materialized views or cron jobs to precompute and store results in a summary table. This cuts query time to near-zero.
• User Answer Aggregation: Create a view or app-level function to fetch all active answers for a user by joining the metadata table with the appropriate answer tables. This makes it easy to retrieve a user's full questionnaire response.
• Archive Old Data: Move answers for deactivated questions to historical tables to reduce the size of your main answer tables, improving query performance.

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