数据库设计疑问：多分类重复分类项优化及文档表关联咨询

Hey there! Let’s walk through your two database design questions with practical, normalized solutions that avoid common pitfalls.

Absolutely do not repeat the three classification entries with identical attributes—this violates core database normalization principles, leading to unnecessary data redundancy and update anomalies (e.g., if you need to change DocType1, you’d have to edit three separate records, risking inconsistencies).

Here’s a far better approach:

• Split out a dedicated table to store unique attribute sets (let’s call it ClassificationProfiles). This table holds one record for each unique combination of Disciplines, DocType1, and DocType2, with its own primary key (profile_id).
• Modify your Classification table to only store the classification name (Engineering, Client, Vendor) and a foreign key linking to the corresponding profile_id in ClassificationProfiles.

Example table structures:

-- Stores unique attribute combinations
CREATE TABLE ClassificationProfiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    Disciplines VARCHAR(255) NOT NULL,
    DocType1 VARCHAR(255) NOT NULL,
    DocType2 VARCHAR(255) NOT NULL
);

-- Links classification names to their shared attribute profiles
CREATE TABLE Classifications (
    classification_id INT PRIMARY KEY AUTO_INCREMENT,
    classification_name VARCHAR(255) UNIQUE NOT NULL,
    profile_id INT NOT NULL,
    FOREIGN KEY (profile_id) REFERENCES ClassificationProfiles(profile_id)
);

This way, updating attributes only requires editing one record in ClassificationProfiles, and all linked classifications automatically inherit the change—no redundancy, no errors.

Your design can work, but it depends on the relationship between category_id and classification_id:

• If category_id maps to a higher-level category table (e.g., Categories with entries like "Internal" or "External", where Classifications are subcategories), then including both foreign keys in Documents is acceptable—just ensure you add proper foreign key constraints to enforce referential integrity.
• If category_id and classification_id are part of the same hierarchy (e.g., Category is the parent of Classification), you don’t need to store category_id directly in Documents. Instead, link Documents only to classification_id, then join with Classifications and Categories when you need the parent category. This eliminates redundant data and prevents mismatches (e.g., a document’s category_id conflicting with its classification_id’s parent category).

Here’s a clean, normalized example for the hierarchy scenario:

-- Parent category table
CREATE TABLE Categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(255) UNIQUE NOT NULL
);

-- Subclassification table linked to parent categories
CREATE TABLE Classifications (
    classification_id INT PRIMARY KEY AUTO_INCREMENT,
    classification_name VARCHAR(255) UNIQUE NOT NULL,
    category_id INT NOT NULL,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

-- Documents only link to the specific classification
CREATE TABLE Documents (
    document_id INT PRIMARY KEY AUTO_INCREMENT,
    document_name VARCHAR(255) NOT NULL,
    classification_id INT NOT NULL,
    FOREIGN KEY (classification_id) REFERENCES Classifications(classification_id)
);

To get a document’s category, just run a JOIN query:

SELECT d.document_name, c.category_name
FROM Documents d
JOIN Classifications cls ON d.classification_id = cls.classification_id
JOIN Categories c ON cls.category_id = c.category_id;

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