Django/SQL多实体关联场景下关联表的创建方法及合理性咨询

嘿，我来帮你拆解这个问题，先从第二个问题开始说，再讲具体的实现方式～

2. 使用关联表是否为该场景下的正确解决方案？ #

绝对是合适的！你的场景需要把**四个明确关联的实体（个人-其公司、代理个人-代理公司）**封装成一组关系，单独的关联表（或者叫关系模型）刚好能满足这个需求：

• 符合数据库设计的第三范式，避免数据冗余；
• 方便后续查询这一组关联关系（比如快速找到某个人对应的公司和代理信息）；
• 便于扩展，如果以后需要给这组关系加额外属性（比如关联生效日期、备注），直接在关联表里加字段就行。

1. 如何在Django Model或SQL中创建此类关联表？ #

方式一：Django Model 实现 #

根据你的现有模型，有两种思路，推荐第一种（直接关联Person和LtdCo），因为更直观且能利用Django的模型验证：

思路1：直接关联Person和LtdCo模型

这种方式不需要额外验证实体类型，因为Person和LtdCo本身就通过OneToOne关联到对应的Entity类型，天然符合你的需求：

from django.db import models
from django.core.exceptions import ValidationError
from django.utils.translation import gettext_lazy as _

class Entity(models.Model):
    class EntityTypes(models.TextChoices):
        PERSON = 'Person', _('Person')
        COMPANY = 'Company', _('Company')
    entity_type = models.CharField(
        verbose_name='Legal Entity Type',
        max_length=7,
        choices=EntityTypes.choices,
        default=EntityTypes.PERSON,
        blank=False,
        null=False)

class Person(models.Model):
    related_entity = models.OneToOneField(Entity, on_delete=models.CASCADE)
    first_name = models.CharField(verbose_name='First Name', max_length=50, blank=True, null=True)
    last_name = models.CharField(verbose_name='Last Name', max_length=50, blank=True, null=True)

class LtdCo(models.Model):
    related_entity = models.OneToOneField(Entity, on_delete=models.CASCADE)
    company_name = models.CharField(verbose_name='Company Name', max_length=50, blank=True, null=True)
    company_no = models.CharField(verbose_name='Company No.', max_length=50, blank=True, null=True)

# 新增的关联模型
class EntityRelationship(models.Model):
    # 拥有有限公司的个人
    profile_person = models.ForeignKey(
        Person, 
        on_delete=models.CASCADE, 
        related_name='owned_company_relationships',
        verbose_name='Profile Person'
    )
    # 该个人对应的有限公司
    profile_ltd_co = models.ForeignKey(
        LtdCo, 
        on_delete=models.CASCADE, 
        related_name='owner_relationships',
        verbose_name='Profile Ltd Co.'
    )
    # 作为代理的个人
    agent_person = models.ForeignKey(
        Person, 
        on_delete=models.CASCADE, 
        related_name='agent_company_relationships',
        verbose_name='Agent Person'
    )
    # 该代理对应的有限公司
    agent_ltd_co = models.ForeignKey(
        LtdCo, 
        on_delete=models.CASCADE, 
        related_name='agent_relationships',
        verbose_name='Agent Ltd Co.'
    )

    class Meta:
        # 添加唯一约束，避免重复的关联组合
        constraints = [
            models.UniqueConstraint(
                fields=['profile_person', 'profile_ltd_co', 'agent_person', 'agent_ltd_co'],
                name='unique_entity_relationship'
            )
        ]
        verbose_name = 'Entity Relationship'
        verbose_name_plural = 'Entity Relationships'

思路2：基于Entity模型关联（需额外类型验证）

如果想统一基于Entity表关联，需要在保存时验证每个关联的Entity类型是否正确：

class EntityRelationship(models.Model):
    profile_entity = models.ForeignKey(
        Entity, 
        on_delete=models.CASCADE, 
        related_name='profile_relationships',
        verbose_name='Profile Entity'
    )
    profile_company_entity = models.ForeignKey(
        Entity, 
        on_delete=models.CASCADE, 
        related_name='profile_company_relationships',
        verbose_name='Profile Ltd Co. Entity'
    )
    agent_entity = models.ForeignKey(
        Entity, 
        on_delete=models.CASCADE, 
        related_name='agent_relationships',
        verbose_name='Agent Entity'
    )
    agent_company_entity = models.ForeignKey(
        Entity, 
        on_delete=models.CASCADE, 
        related_name='agent_company_relationships',
        verbose_name='Agent Ltd Co. Entity'
    )

    def clean(self):
        # 验证每个Entity的类型是否符合要求
        if self.profile_entity.entity_type != Entity.EntityTypes.PERSON:
            raise ValidationError("Profile Entity must be a Person type")
        if self.profile_company_entity.entity_type != Entity.EntityTypes.COMPANY:
            raise ValidationError("Profile Company Entity must be a Company type")
        if self.agent_entity.entity_type != Entity.EntityTypes.PERSON:
            raise ValidationError("Agent Entity must be a Person type")
        if self.agent_company_entity.entity_type != Entity.EntityTypes.COMPANY:
            raise ValidationError("Agent Company Entity must be a Company type")

    def save(self, *args, **kwargs):
        # 保存前先执行验证
        self.full_clean()
        super().save(*args, **kwargs)

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['profile_entity', 'profile_company_entity', 'agent_entity', 'agent_company_entity'],
                name='unique_entity_rel_by_entity'
            )
        ]

方式二：SQL 直接创建表 #

如果需要直接用SQL创建关联表，对应上面两种思路：

对应思路1的SQL（关联Person和LtdCo）

CREATE TABLE entity_relationship (
    id SERIAL PRIMARY KEY,
    profile_person_id INTEGER REFERENCES person(id) ON DELETE CASCADE,
    profile_ltd_co_id INTEGER REFERENCES ltd_co(id) ON DELETE CASCADE,
    agent_person_id INTEGER REFERENCES person(id) ON DELETE CASCADE,
    agent_ltd_co_id INTEGER REFERENCES ltd_co(id) ON DELETE CASCADE,
    -- 唯一约束，防止重复关联组合
    CONSTRAINT unique_entity_relationship UNIQUE (profile_person_id, profile_ltd_co_id, agent_person_id, agent_ltd_co_id)
);

对应思路2的SQL（关联Entity表，带类型检查）

以PostgreSQL为例（MySQL需要用触发器实现类似检查）：

CREATE TABLE entity_relationship (
    id SERIAL PRIMARY KEY,
    profile_entity_id INTEGER REFERENCES entity(id) ON DELETE CASCADE,
    profile_company_entity_id INTEGER REFERENCES entity(id) ON DELETE CASCADE,
    agent_entity_id INTEGER REFERENCES entity(id) ON DELETE CASCADE,
    agent_company_entity_id INTEGER REFERENCES entity(id) ON DELETE CASCADE,
    -- 检查约束，确保每个Entity的类型正确
    CONSTRAINT chk_profile_person CHECK (
        (SELECT entity_type FROM entity WHERE id = profile_entity_id) = 'Person'
    ),
    CONSTRAINT chk_profile_company CHECK (
        (SELECT entity_type FROM entity WHERE id = profile_company_entity_id) = 'Company'
    ),
    CONSTRAINT chk_agent_person CHECK (
        (SELECT entity_type FROM entity WHERE id = agent_entity_id) = 'Person'
    ),
    CONSTRAINT chk_agent_company CHECK (
        (SELECT entity_type FROM entity WHERE id = agent_company_entity_id) = 'Company'
    ),
    -- 唯一约束
    CONSTRAINT unique_entity_rel_by_entity UNIQUE (profile_entity_id, profile_company_entity_id, agent_entity_id, agent_company_entity_id)
);

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