如何使用通用关系表实现数据库跨表关联？

Great question! Let's break down your problem into two core parts: handling cross-table associations without restrictive foreign keys, and eliminating redundant bidirectional relationship records. Here's a practical, scalable solution that aligns with your requirement to avoid inheritance-based empty columns:

1. Solving the Generic Association (Foreign Key Limitation) #

Since standard foreign keys can only reference a single table, we need a way to track which entity type each ID belongs to while maintaining referential integrity as much as possible. Here's how to implement it:

Step 1: Create an Entity Type Lookup Table #

First, define a table to catalog all entities that can be related (Item, Tag, Feature, etc.):

CREATE TABLE IF NOT EXISTS `Entity_Type` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `type_name` VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'Item', 'Tag', 'Feature'
    `table_name` VARCHAR(50) NOT NULL UNIQUE, -- Optional: Store actual DB table name for validation
    PRIMARY KEY (`id`)
);

The table_name column is optional but useful for validation logic (e.g., mapping type ID 1 to the Item table).

Step 2: Refactor the Relationship Table #

Update your Relationship table to link to the Entity_Type table, and add indexes to optimize queries:

CREATE TABLE IF NOT EXISTS `Relationship` ( 
    `id` INT(11) NOT NULL AUTO_INCREMENT, 
    `relater_id` INT(11) NOT NULL, 
    `relatee_id` INT(11) NOT NULL, 
    `relationship` ENUM('requires', 'mutually_requires', 'required_by', 'relates', 'excludes') NULL DEFAULT NULL, 
    `description` VARCHAR(1000) NULL DEFAULT NULL, 
    `relater_type_id` INT(11) NOT NULL, 
    `relatee_type_id` INT(11) NOT NULL, 
    PRIMARY KEY (`id`), 
    -- Indexes for fast lookups by entity type + ID
    INDEX `idx_relater` (`relater_type_id`, `relater_id`),
    INDEX `idx_relatee` (`relatee_type_id`, `relatee_id`),
    -- Foreign keys to the Entity_Type table (enforces valid entity types)
    FOREIGN KEY (`relater_type_id`) REFERENCES `Entity_Type`(`id`),
    FOREIGN KEY (`relatee_type_id`) REFERENCES `Entity_Type`(`id`),
    -- Optional: Prevent duplicate identical relationships
    UNIQUE KEY `uq_unique_relationship` (`relater_type_id`, `relater_id`, `relatee_type_id`, `relatee_id`, `relationship`)
);

Step 3: Maintain Referential Integrity #

Since we can't use foreign keys directly to entity tables, you have two reliable options:

• Application Layer Validation: Before inserting/updating a relationship, check that relater_id exists in the table corresponding to relater_type_id (and same for relatee_id).
• Database Triggers: Write triggers that run on INSERT/UPDATE to validate the existence of the entity ID in the correct table. For example, a trigger that checks the Item table if relater_type_id maps to 'Item'.

2. Eliminating Redundant Bidirectional Records #

Redundancy happens when you store both A excludes B and B excludes A. Here's how to fix this:

For Symmetric Relationships (mutually_requires, excludes, relates) #

These relationships are bidirectional by definition, so you only need to store one record. Enforce this with a check constraint (or trigger, if your DB doesn't support check constraints like MySQL pre-8.0):

-- Add this to the Relationship table (MySQL 8.0+ or PostgreSQL)
CHECK ((relater_type_id < relatee_type_id) OR 
       (relater_type_id = relatee_type_id AND relater_id < relatee_id))

This ensures we only store the "lexicographically smaller" pair (e.g., if A has type 1, ID 5 and B has type 2, ID 3, we store B->A instead of A->B). When querying, look for either the pair as stored or reversed:

-- Get all relationships for an entity (type 1, ID 5)
SELECT * FROM Relationship
WHERE (relater_type_id = 1 AND relater_id = 5) OR 
      (relatee_type_id = 1 AND relatee_id = 5);

For Asymmetric Relationships (requires, required_by) #

These are inverses of each other—A requires B is the same as B required_by A. Instead of storing both, pick one to store (e.g., only requires) and derive the inverse in queries:

-- Get all entities that require item 5
SELECT relatee_type_id, relatee_id FROM Relationship
WHERE relater_type_id = 1 AND relater_id = 5 AND relationship = 'requires';

-- Get all entities that item 5 requires (i.e., required_by)
SELECT relater_type_id, relater_id FROM Relationship
WHERE relatee_type_id = 1 AND relatee_id = 5 AND relationship = 'requires';

Simplify Queries with a View #

To avoid writing complex SQL every time, create a view that combines stored records with their inverses:

CREATE VIEW `All_Relationships` AS
-- Original records
SELECT 
    relater_type_id AS entity_type_id,
    relater_id AS entity_id,
    relatee_type_id AS related_type_id,
    relatee_id AS related_id,
    relationship,
    description
FROM Relationship
UNION ALL
-- Inverse records for asymmetric relationships
SELECT 
    relatee_type_id AS entity_type_id,
    relatee_id AS entity_id,
    relater_type_id AS related_type_id,
    relater_id AS related_id,
    CASE relationship
        WHEN 'requires' THEN 'required_by'
        WHEN 'required_by' THEN 'requires'
        ELSE relationship
    END AS relationship,
    description
FROM Relationship
WHERE relationship NOT IN ('mutually_requires', 'excludes', 'relates'); -- Skip symmetric ones to avoid duplicates

Now you can query this view like a single, complete set of relationships without worrying about redundancy.

About Your Supplemental Question: Base Types #

You're right that inheritance (like single-table inheritance with empty columns) isn't a good fit here—your goal is to link arbitrary entities without coupling their schemas. The Entity_Type + Relationship table approach is exactly what you need: it only stores IDs and type references, no empty columns, and supports any number of entity types.

Final Recommendation #

The best approach combines:

1. An Entity_Type table to track all relatable entities.
2. A refactored Relationship table with type references and indexes.
3. Application-layer or trigger-based validation for referential integrity.
4. Constraints/triggers to avoid redundant symmetric relationships.
5. A view to simplify bidirectional relationship queries.

This solution is flexible, scalable, and avoids the pitfalls of inheritance or overly complex schemas.

内容的提问来源于stack exchange，提问作者C.Programming