关于两个实体间多关系的ER Sketch可行性及数据重复问题咨询

Hey there! Let’s tackle your question about multiple relationships between two entities and the supposed data duplication risk head-on.

Absolutely yes—this is a common and perfectly valid scenario in database design, especially when modeling real-world business logic.

For example:

• A Customer and Product might have two distinct relationships: one where the customer purchased the product, and another where they reviewed it.
• An Employee and Department could have both a "works in" relationship (the employee is assigned to the department) and a "manages" relationship (the employee oversees the department).
• A User and Post might have a "created" relationship (the user wrote the post) and a "liked" relationship (the user liked the post).

ER diagrams explicitly support this by showing multiple relationship lines between entity pairs, each labeled to describe the specific association.

Short answer: No, not if you design your schema correctly. The risk of data duplication comes from poor schema design, not from the existence of multiple relationships themselves.

Let’s break this down with examples:

Bad Design (Leads to Duplication) #

If you cram multiple relationships into a single table and redundantly store entity attributes, you’ll end up with duplicate data:

-- ❌ Poor design: Mixes purchase and review data, duplicates customer/product details
CREATE TABLE customer_product_mess (
    customer_id INT,
    customer_name VARCHAR(50), -- Redundant! Should live in a customers table
    product_id INT,
    product_name VARCHAR(50), -- Redundant! Should live in a products table
    purchase_date DATE,
    review_text TEXT
);

Here, if a customer both buys and reviews a product, you’ll have to store their name and the product’s name twice—leading to duplication and update anomalies (e.g., changing the customer’s name would require updating every row they appear in).

Good Design (Avoids Duplication) #

Follow database normalization rules (especially 3NF) to separate entities and relationships properly. You have two solid options:

Option 1: Separate Junction Tables for Each Relationship

Create a dedicated junction table for each distinct relationship. Entity attributes are stored only once in their base tables:

-- ✅ Proper normalized design
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

-- Junction table for "purchased" relationship
CREATE TABLE customer_purchases (
    customer_id INT REFERENCES customers(customer_id),
    product_id INT REFERENCES products(product_id),
    purchase_date DATE,
    PRIMARY KEY (customer_id, product_id)
);

-- Junction table for "reviewed" relationship
CREATE TABLE customer_reviews (
    customer_id INT REFERENCES customers(customer_id),
    product_id INT REFERENCES products(product_id),
    review_text TEXT,
    review_date DATE,
    PRIMARY KEY (customer_id, product_id)
);

This way, customer and product details are stored once, and each relationship tracks only the relevant metadata (purchase date, review text). No duplication here.

Option 2: Single Junction Table with Relationship Type

If your relationships share some attributes, you can use a single junction table with a relation_type column to distinguish between them. Add constraints to ensure only relevant fields are populated for each type:

-- ✅ Flexible design for related relationships
CREATE TABLE customer_product_relations (
    customer_id INT REFERENCES customers(customer_id),
    product_id INT REFERENCES products(product_id),
    relation_type VARCHAR(20) CHECK (relation_type IN ('PURCHASED', 'REVIEWED')),
    purchase_date DATE, -- Only required for PURCHASED
    review_text TEXT, -- Only required for REVIEWED
    PRIMARY KEY (customer_id, product_id, relation_type)
);

This keeps your schema concise while still avoiding redundant entity data.

Multiple relationships between two entities are not inherently problematic—they’re a tool to model complex real-world interactions. Data duplication occurs only when you violate normalization principles by storing entity attributes in junction tables or mixing unrelated data in a single table. By keeping entity data in their own base tables and using dedicated junction tables (or a typed junction table) for relationships, you can avoid duplication entirely.

内容的提问来源于stack exchange，提问作者Imanka Priyasad Amarakoon