1

From questions like this one, I know that the recommended way to make optional foreign keys in django is to set null=True, blank=True. That way, the value of the foreign key does not have to be set.

This seems fine to me if the foreign key is frequently used in the model. But if the majority of the foreign keys are null values, then wouldn't this violate first normal form and create a lot of wasted space?

Sure, the topic of null values in databases may be controversial, but I still wonder how I should set up optional foreign keys in Django if I know that the foreign key will be sparse. Does Django already take this into account? Should I create a separate model for this relationship? Has this already been taken into account during the design of Django?

Thanks.

Community
  • 1
  • 1
Roman
  • 176
  • 3
  • 10
  • In my opinion it is perfectly fine to have nullable foreign keys if the real life problem requires it. Actually, this is a general relational database question and not a Django specific one. You can get a better answer by asking it at http://dba.stackexchange.com/ – Selcuk Feb 13 '15 at 13:15
  • I would argue that this is still a django-specific question. This may be related to the nullable columns debate, but I'm more curious on how to approach this in Django. I'm okay with using null values, but if I want to avoid **a lot** of null values, how should should I do that in Django? – Roman Feb 13 '15 at 13:28

1 Answers1

1

You can simulate a nullable foreign key by using a third table:

class City(models.Model):
    name = models.CharField(max_length=80)

class Person(models.Model):
    name = models.CharField(max_length=80)

class PersonCity(models.Model):
    person = models.ForeignKey(Person, unique=True)
    city = models.ForeignKey(City)

This way, you will only create a row in the table PersonCity for those people with a known City. To access the city of a given person you would use:

city = person.personcity_set().first().city

You can create a custom manager to shorten this syntax and check for a null personcity_set which I didn't for the sake of example, but I personally think that creating a nullable foreign key is still easier to read and debug.

Selcuk
  • 57,004
  • 12
  • 102
  • 110
  • 2
    Thank you. I had figured as much about creating the third table, but was wondering if there was a more elegant way of doing it, or if Django could optimize the DB structure somehow. It's a shame that this method is so clunky--I will probably end up doing the nullable foreign key anyway. – Roman Feb 13 '15 at 18:08