Ensuring uniqueness across multiple models Django

TheSunbathingCrow 0
Viewed 74

I have three models: Account, Employee and Company. An account can be of the type employee or company and a OneToOne Field relates an account to either an employee or company record. See models below:

class Account(auth_models.AbstractBaseUser):

     username = models.CharField(max_length=40, unique=True)

     ACCOUNT_TYPE_CHOICES = (
         ("company", "Company"),
         ("employee", "Employee"),
     )

     account_type = models.CharField(
         choices=ACCOUNT_TYPE_CHOICES,
         default="EMPLOYEE",
         blank=False,
         null=False,
         max_length=10,
      )
     date_joined = models.DateTimeField(verbose_name="Date Joined", auto_now_add=True)
     is_active = models.BooleanField(default=True)
     is_superuser = models.BooleanField(default=False)

     USERNAME_FIELD = "username"
     REQUIRED_FIELDS = ["account_type"]

I then have the Company model:

class Company(models.Model):
    user = models.OneToOneField(Account, on_delete=models.CASCADE, unique=True)
    name = models.CharField(max_length=75, blank=False, null=False, unique=True)

Then I have the Employee model, where an Employee is also related to the company it is employed by:

class Employee(models.Model):
     user = models.OneToOneField(Account, on_delete=models.CASCADE, unique=True)
     first_name = models.CharField(max_length=50, null=False, blank=False)
     last_name = models.CharField(max_length=50, null=False, blank=False)
     date_of_birth = models.DateField(null=False, blank=False)
     employer = models.ForeignKey(
         Company, on_delete=models.CASCADE, related_name="employees"
     )
     employment_start_date = models.DateField(null=False, blank=False)
     employment_end_date = models.DateField(blank=True, null=True)

I want to make sure usernames are unique within the company, but not unique within the whole web app. So there can be multiple users with the same username, but not within the same company. For example: Suppose a user created an account with the username John Smith with the company ABC that will be fine.

But then if another user created an account with the username John Smith but in the company DEF that will also be allowed as it is in another company.

But if someone tries to make an account with the username John Smith within the company ABC, that will not be allowed as it already exists with that company

Solutions I've thought of so far:

  1. Adding a company field to the Account model - the issue with this being that an Account with the account_type "Company" would not have a value for the company field it would be illogical. Also it would be illogical as via relationships you can find the company an employee belongs to in the Employee table/model.
  2. Getting the "employees" field from the Company model and checking if the user already exists within that field as it is exclusive to the company - I have no idea how to implement something like this.
  3. Using emails instead - I wanted to originally do this, but people within my team want us to use usernames instead as not everyone interacting with the system will have an email.

Any help on how I could implement something like this would be great. Thanks :)

P.S I'm using Postgres

django-models django-rest-framework django python-3.x python
1 Answers
  1. Adding a company field to the Account model - the issue with this being that an Account with the account_type "Company" would not have a value for the company field it would be illogical. Also it would be illogical as via relationships you can find the company an employee belongs to in the Employee table/model.

Why would a Company have an empty company field? OP can have it with the company field as well since OP has the field account_type to distinguish between the types of Accounts (generally I prefer to have fields similar to account_type as Boolean, so something like is_company).

Also, OP can't find the company an employee belongs to from the Employee model if OP removes the employer and take that the employer is implicit in the company of the Account. So, if OP goes with this approach, then remove it as well. (Well, OP could but OP would then have to go through the user/Account).

As a way to clarify OP's comment, like I explained in previously, one can have the Account model with

  • is_company BooleanField -> this will tell which kind of account you have.
  • company ForeignKey -> this will tell the company for both types of accounts.

The Company model with

  • name
  • other company information (do not add the user OneToOneField here)

Then in Employee one won't need the employer since one can infer that from the Account. But onr will have to add

  • user OneToOneField -> Because one account has zero or one and only one employee.
  1. Getting the "employees" field from the Company model and checking if the user already exists within that field as it is exclusive to the company - I have no idea how to implement something like this.

Initially I though one could use unique_together (or UniqueConstraint) to the Employee model, like

class Meta:
        unique_together = [["employer", "user__username"]]

Yet, as per Abdul Aziz Barkat

you cannot create constraints spanning relations / tables

So, a workaround can be to override the model's save() method to perform the validation. Read more about overriding model predefined methods.

Still, this doesn't solve the fact that an Account is created first than an Employee ATM.

Since to create an Account OP is already checking for a unique username, the uniqueness is being enforced previous to the Employee.

As AKX mentions, there's a workaround, more precisely, creating the Account and Employee in a transaction. That implies OP is able to remove unique=True in Accounts, which, according to the documentation

The field must be unique (i.e., have unique=True set in its definition), unless you use a custom authentication backend that can support non-unique usernames.

  1. Using emails instead - I wanted to originally do this, but people within my team want us to use usernames instead as not everyone interacting with the system will have an email.

Well people within OP's team might not be aware of the implications such request brings. Even though a framework like Django brings flexibility in various levels, such as database migrations, it also has its ways which might be unknown to your team. You can have a meeting and present the different options that OP considered and it's implications and I'm sure they are able to understand.

Gonçalo Peres0
Related