0

Some time ago someone told me that nullable ForeignKeys are "evil" database design?

In other words it is an "anti-pattern", something you should avoid.

Why?

guettli
  • 25,042
  • 81
  • 346
  • 663
  • 1
    @BrankoDimitrijevic please elaborate why are they not "evil"? – guettli Dec 18 '15 at 11:33
  • Because they model a common and useful case (_optional_ reference). I'm not aware of a better way to model that case. Are you? – Branko Dimitrijevic Dec 18 '15 at 11:39
  • @BrankoDimitrijevic For places where we used nullable ForeignKeys in the past, we now use a default. Example: User---Country: We insert a country called "Unknown". If the country of a user is unknown this gets used as FK. – guettli Dec 18 '15 at 14:32
  • How do you know the "Unknown" row is special? Hard-code it in the business logic? And what values do you use for the other fields of the "Unknown" row? For example, what is a sensible country code, or a phone number of an "Unknown" country? What you describe is _a_ solution, but I wouldn't call it a _better_ solution. – Branko Dimitrijevic Dec 20 '15 at 11:02
  • @BrankoDimitrijevic we don't have a phone number of our countries in our model. But you are right here, if we would have one. The "unknown" value now is in the phone-number. Did I say it was "better"? Then I am sorry ("Out beyond ideas of wrongdoing and rightdoing, there is a field. I'll meet you there."). I would call my solution "an other strategy". – guettli Dec 21 '15 at 08:54

1 Answers1

0

You need an "if" to process a datastructure which has a nullable ForeignKey.

You can read more about why "if" is evil here: Why is the 'if' statement considered evil?

Community
  • 1
  • 1
guettli
  • 25,042
  • 81
  • 346
  • 663
  • 1
    There is no `if` in SQL and you certainly don't need one to select rows with nullable foreign keys. –  Dec 18 '15 at 09:13
  • 1
    @a_horse_with_no_name of course you are right: There is no "if" in SQL. But in any programming language you need to decide: "Does this variable contain data or is it NULL". – guettli Dec 18 '15 at 09:28
  • And how is this specific to foreign keys? Other columns can be null as well. –  Dec 18 '15 at 09:29
  • 1
    @a_horse_with_no_name yes, other columns could be nullable as well. .... I think the same rule applies. Handling nullable columns in a programming language is annoying. – guettli Dec 18 '15 at 10:13