mailman_73 mailman_73 - 1 year ago 50
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.

Answer Source

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()