mailman_73 mailman_73 - 2 months ago 8
SQL Question

How to avoid two ForeignKeys in django model if one of them will be NULL in every case

The prologue:

I've got models called:

  • Supervisor

  • Salesman

  • Car

  • Motorcycle

  • Product (the abstract model for Car and Motorcycle)

A salesman and a supervisor can sell cars. A supervisor also controlls work of two or more salesmen.

What do I do:
I want to create the fittest model to that task.

The problem:
The problem is that a supervisor can controll salesmen and sell products. I thought to create an abstract model called
which will be share fields between
will have
. But I can't make
to an abstract model.

I don't want have
as a table with whether a salesman field or a supervisor field is empty. And I don't want to use generic
because it's to complex.

My best guess: Add John Smith, for example, as a supervisor and a salesman. In this case John Smith salesman has ForeignKey to John Smith a supervisor. But I think there are the best solution.

The simplest but bad decision.

class Product(models.Model):
company = models.ForeignKey(Company, verbose_name = 'Car company');
price = models.IntegerField()
horse_power = models.IntegerField()
salesman = models.ForeignKey(Salesman, null=True)
supervisor = models.ForeignKey(Supervisor, null=True)

class Meta:
abstract = True

class Car(Product):
number_of_doors = models.IntegerField()
is_conditioner = models.NullBooleanField()

class Supervisor(models.Model):
name = models.CharField(max_length=100)

class Salesman(models.Model):
name = models.ChartField(max_length=100)
supervisor = models.ForeignKey(Supervisor)

My question is what is the best way to rewrite model structure to avoid two ForeignKeys if one of them will be NULL in every case and avoid
to abstract model.


I think a simple solution would be to combine Supervisor and Salesman into 1 model. You could simply call this Staff and create a field called is_supervisor which would differentiate supervisors from regular salesmen.

To do this, you would set up your model something like this:

class Product(models.Model):
  staff = models.ForeignKey(Staff, null=True)
  # other stuff

class Staff(models.Model):
  name = models.CharField(max_length=100)
  supervisor = models.ForeignKey('self', null=True)
  is_supervisor = models.BooleanField()