mailman_73 mailman_73 - 3 months ago 12
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
'Staff'
which will be share fields between
Salesman
and
Supervisor
and
Product
will have
ForeignKey
to
Staff
. But I can't make
ForeignKey
to an abstract model.

I don't want have
Product
as a table with whether a salesman field or a supervisor field is empty. And I don't want to use generic
ForeignKey
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
ForeignKey
to abstract model.

Answer

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