单表仅能与另外两表之一关联的数据库关联设计疑问

Hey there, let's tackle this specific database design scenario you're facing—where each record in your attribute table needs to link to either a visit or a visit_action (but never both). I've worked through similar cases before, so here are the most practical approaches tailored to your setup:

1. Mutually Exclusive Foreign Keys (Most Straightforward) #

• Add two foreign key columns to your attribute table: visit_id (linking to visit.id) and visit_action_id (linking to visit_action.id)
• Enforce the "only one can be non-null" rule at the database level with a check constraint:

  ALTER TABLE attribute
  ADD CONSTRAINT chk_attribute_single_parent
  CHECK ((visit_id IS NOT NULL AND visit_action_id IS NULL) OR (visit_id IS NULL AND visit_action_id IS NOT NULL));
  

• Pros: Super easy to understand and implement. Queries can directly join using the non-null column without extra logic.
• Cons: Less scalable if you ever need to add a third table that attribute could link to—you'd have to add another foreign key column and update the check constraint.

2. Generic Entity Type + ID (Scalable Option) #

• Add two columns to attribute:
  • entity_type: An enum (or varchar with a check constraint) restricted to values like 'visit' or 'visit_action'
  • entity_id: A value matching the primary key of the linked table
• Pros: Extremely flexible—if you add a new related table later, you just expand the enum values instead of modifying the table schema.
• Cons: No built-in foreign key enforcement for entity_id (you'll need triggers or application-level logic to ensure the ID exists in the correct table). Queries require conditional joins, like this:

  SELECT a.name, a.value, COALESCE(v.id, va.id) AS parent_id, COALESCE(v.name, va.action_name) AS parent_name
  FROM attribute a
  LEFT JOIN visit v ON a.entity_type = 'visit' AND a.entity_id = v.id
  LEFT JOIN visit_action va ON a.entity_type = 'visit_action' AND a.entity_id = va.id;
  

3. Base Table Inheritance (Strictly Normalized) #

• Create a shared base table, say attribute_owner, with just a primary key id
• Update visit and visit_action to either inherit from attribute_owner (if your DB supports table inheritance, like PostgreSQL) or add an attribute_owner_id foreign key linking to this base table
• Then attribute only needs a single foreign key attribute_owner_id linking to attribute_owner.id
• Pros: Fully normalized, and foreign key constraints guarantee that every attribute links to a valid parent record (no orphaned attributes).
• Cons: Adds an extra layer of complexity, especially if your database doesn't support native inheritance. Overkill if you don't anticipate adding more parent tables down the line.

Quick Recommendation #

If your use case is stable (no plans to add more parent tables for attribute), go with Option 1—it's simple and keeps data integrity enforced at the database level. If you need flexibility for future changes, Option 2 is the way to go.

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