无编号ID的多对多自引用：标签实体关系替代方案问询

Great question—this is a super common pain point when dealing with symmetric relationships (like tag associations where linking A to B is identical to B to A) in relational databases. I’ve wrestled with this exact issue building content tagging systems, so let’s break down practical alternatives that avoid the dual-query or redundant row problems you’re frustrated with:

1. Enforce Ordered Relationships in a Minimal Join Table #

If you still want to stick with an RDBMS, you can eliminate redundancy by enforcing a strict order on your join table’s IDs. Here’s how:

• Add a CHECK constraint to your join table that ensures tag_id1 < tag_id2 (never store reversed pairs like (B,A) if (A,B) already exists).
• Handle ordering at insert time—either in your application layer (sort the two tag IDs before writing) or with a database trigger that swaps IDs if needed.
• Query all related tags with a single, simple statement that checks both columns:

-- Create the constrained join table
CREATE TABLE tag_relations (
    tag_id1 INT REFERENCES tags(id),
    tag_id2 INT REFERENCES tags(id),
    PRIMARY KEY (tag_id1, tag_id2),
    CHECK (tag_id1 < tag_id2)
);

-- Query all tags related to ID 5
SELECT t.* 
FROM tags t
JOIN tag_relations tr ON t.id = tr.tag_id1 OR t.id = tr.tag_id2
WHERE tr.tag_id1 = 5 OR tr.tag_id2 = 5;

This eliminates duplicate rows and only requires one query to fetch all related tags.

2. Use Native Collection/JSON Types (Database-dependent) #

Many modern RDBMS support native array or JSON types that let you store related tag IDs directly on the tags entity, no join table required:

• For PostgreSQL, use an INT[] array column to store related tag IDs.
• For MySQL or SQL Server, use a JSON column to store an array of IDs.
• To maintain symmetry, add logic (either in your app or via database triggers) that updates both sides of the relationship when a new association is added.

Example with PostgreSQL:

CREATE TABLE tags (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    related_tags INT[] NOT NULL DEFAULT '{}'
);

-- Add a symmetric association between tag 1 and 2
UPDATE tags SET related_tags = array_append(related_tags, 2) WHERE id = 1;
UPDATE tags SET related_tags = array_append(related_tags, 1) WHERE id = 2;

-- Fetch all tags related to ID 1
SELECT * FROM tags WHERE id = 1 OR id = ANY(SELECT unnest(related_tags) FROM tags WHERE id = 1);

This simplifies your schema but requires careful maintenance to keep relationships symmetric.

3. Switch to a Graph Database #

If you’re open to moving beyond RDBMS entirely, graph databases like Neo4j are built explicitly for this kind of relationship-heavy data:

• Represent each tag as a node in the graph.
• Represent associations as undirected edges between nodes (no need for ID pairs—edges are inherently symmetric).
• Query all related tags in one step with a graph-specific query language like Cypher:

-- Match tag with ID 5 and all directly related tags
MATCH (tag:Tag {id: 5})--(related:Tag)
RETURN related;

Graph databases eliminate the need for join tables entirely and make complex relationship queries trivial.

4. Abstract the Logic in Your Application Layer #

If you can’t change your storage system, wrap the messy query logic in a reusable application layer component (like a DAO or repository class):

• Create a method like getRelatedTags(tagId) that internally runs the dual-column query or handles deduplication.
• Your business code never sees the underlying tag_id1/tag_id2 structure—it just gets a clean list of related tags.

For example, in Python with SQLAlchemy:

def get_related_tags(session, tag_id):
    query = session.query(Tag).join(
        TagRelation,
        (Tag.id == TagRelation.tag_id1) | (Tag.id == TagRelation.tag_id2)
    ).filter(
        (TagRelation.tag_id1 == tag_id) | (TagRelation.tag_id2 == tag_id)
    ).filter(Tag.id != tag_id)
    return query.distinct().all()

This hides the complexity from your core application code and lets you swap out the storage implementation later if needed.

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