I'm building the following data model in Django / SQL

Table 1 (Entity) - things that can do things
Entity ID:
Enum: (Person, or Business)

Table 2 (Person)
Entity ID:
Person ID:
Firstname
Lastname

Table 3 (Ltd Co.)
Entity ID:
Ltd Co. ID:
Company Name
Company No.

I'm trying to link a Person, who has a Limited Co., with an Agent (who is also a Person) that also has a Limited Co. So 4 different entitites, two of which are type Person, and two of which are type Limited Co.

Do I need another Linkage Table? E.g.

Q. How do I create this Linkage Table in a Django Model / or SQL?

Q. Is a Linkage Table the right approach?

UPDATE

Here are snippets from my model.py

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)

CodePudding user response：

I think what you need is ForeignKey. You can link one Person object to another one. Just add this:

class Person(models.Model):
    ...
    agent = models.ForeignKey('self', on_delete=models.SET_NULL, null=True, related_name='clients')

From now on, you can simply add one Person as an agent in another Person's field. Usage:

person_1 = Person.objects.create(...)
person_2 = Person.objects.create(..., agent=person_1)

person_1.clients.all()
# <QuerySet [<Person: Person 2>]>

person_2 in person_1.clients.all()
# True

person_2.agent
# <Person: Person 1>

person_2.agent.related_entity.entity_type
# 'Company' / 'Person'

You can access related object by simple .agent or reverse relation of all clients with the related_name you want to use, in my example it's .clients. Just remember, that reverse relation is always a QuerySet, because one agent can have many clients, but every client can have one agent. Relationhips are very powerful in Django, follow this DOCS for details.