[Django]-OneToManyRelationship in a django model

3👍

✅

Well the question is how to model this properly.

This is in fact not a many-to-many relation. This is a one-to-many relation: one city can have zero or more companies, and a company can have zero or more emails and telephone numbers.

Approach one: two one-to-many relations

We can model this with four entities City, Company, CompanyEmail, and CompanyPhone. Something that looks like this:

+------+ 1     N +---------+ 1     N +--------------+
| City |---------| Company |---------| CompanyEmail |
+------+         +---------+    |    +--------------+
| name |         | name    |    |    | email        |
+------+         +---------+    |    +--------------+
                                |
                                |  N +--------------+
                                 \---| CompanyPhone |
                                     +--------------+
                                     | phone        |
                                     +--------------+

We can define this as:

class City(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)

class Company(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)
    city = models.ForeignKey(City)

class CompanyEmail(models.Model):
    company = models.ForeignKey(Company)
    email = models.EmailField(max_length=100, blank=True, null=True)

class CompanyPhone(models.Model):
    company = models.ForeignKey(Company)
    mobile_number = models.CharField(max_length=10, blank=True, null=True)

A problem with this modeling however is that the number of relations can become quite large if companies have email addresses, phone numbers, addresses, registration numbers, trademarks, etc. As a result the number of models blow up enourmously.

The advantage however is that we can let the models perform there own checks: a CompanyEmail can verify that it is a valid email address, and provide some convenient methods (for example to easily contact the company). If however the number of options is large, then it we have to write some subtantial amount of code.

Pulling up the company connection

We can ease this a bit, by pulling up the relation to the company in an abstract model (for example CompanyAttribute):

class City(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)


class Company(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)
    city = models.ForeignKey(City)

class CompanyAttribute(models.Model):
    company = models.ForeignKey(Company)

    class Meta:
        abstract=True

class CompanyEmail(CompanyAttribute):
    email = models.EmailField(max_length=100, blank=True, null=True)

class CompanyPhone(CompanyAttribute):
    mobile_number = models.CharField(max_length=10, blank=True, null=True)

This does not change anything at the database level, and we still thus defined two one-to-many relations, but here we can encapsulate some logic of a company attribute at the CompanyAttribute level.

Approach two: using an entity-attribute-value table

In case the type of attributes can be dynamic (for example a user can introduce a new type of attribute), then we might introduce a entity-attribute-value model. We here define four models: City, Company, CompanyAttribute, and AttributeKind.

+------+ 1    N +---------+ 1    N +------------------+ N    1 +---------------+
| City |--------| Company |--------| CompanyAttribute |--------| AttributeKind |
+------+        +---------+        +------------------+        +---------------+
| name |        | name    |        | value            |        | attributename |
+------+        +---------+        +------------------+        +---------------+

Or in Django, this could look like:

class City(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)

class Company(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)
    city = models.ForeignKey(City)

class AttributeKind(models.Model):
    attributename = models.CharField(max_length=200, blank=True, null=True)

class CompanyAttribute(models.Model):
    company = models.ForeignKey(Company)
    kind = models.ForeignKey(AttributeKind)
    value = models.CharField(max_length=200, blank=True, null=True)

We thus can then add two AttributeKinds by default: a phone and email, but we can later decide to add more kinds (address, VAT number, etc.).

We can now dynamically construct new kinds of attributes. A potential problem with this approach however is that it is harder to validate that the content actually makes sense. For example a user can store any string as an email address, it is harder to enforce an actual email address.

5👍

In the exact same way as a City can have multiple CompanyDetailses: by putting them in a separate table.

class City(models.Model):
    name = models.CharField(max_length=200, blank=True, null=True)


class CompanyDetails(models.Model):
    com_name = models.ForeignKey(City)


class CompanyEmail(models.Model):
    company = models.ForeignKey(CompanyDetails)
    email = models.EmailField(max_length=100, blank=True, null=True)


# And similar for phone numbers

Alternatively, if you are using PostgreSQL only, take a look at ArrayField.

Leave a comment