14

Say I have the following ER diagram:

enter image description here

Now if I represented the relationship using a foreign key of School in Student, I could have NULL values (because a Student is not required to belong to a School), for example:

enter image description here

So the correct way (based on what I have read) is to create an intersection table to represent the relationship, for example:

enter image description here

This way, no NULL values can be present in the table School_has_Student.

But what are the disadvantages of using a nullable foreign key instead of creating an intersection table?


Edit:

I mistakenly chose (school_id, student_id) to be the primary key for the School_has_Student table, which made the relationship many-to-many. The correct primary key should have been student_id:

enter image description here

  • 7
    There's is no "correct" way. There's just the way that is best for your needs. – MetaFight Nov 3 '16 at 16:54
  • 1
    I agree with Doc about the false premise, but maybe it's still clear enough to answer? – MetaFight Nov 3 '16 at 17:13
  • There is a false premise, but it is easy enough to straighten out and explain the difference. – user22815 Nov 3 '16 at 18:02
  • I retracted my close vote, but the sentence "So the correct way (based on what I have read) is to create an intersection table to represent the relationship" gives me the impression you should tell us which strainge source told you this is the "correct" way. In every text book I have read before, the canonical way for 1:n relationships is a single foreign key. Or did you misunderstand something? – Doc Brown Nov 3 '16 at 18:29
  • @Doc Brown I don't remember where I have read it, but I am sure that it says that an intersection table was the correct way. Anyway, can you give me the name of a book that says that a 1:n relationship (with optional participation on the :1 side) should be represented using a single foreign key, I am interested in reading what they say about this subject. – Tom Nov 3 '16 at 19:31
18

The two models represent different relationships.

By using a join table, you are modeling a many-to-many relationship.

By using a simple foreign key, you are modeling a one-to-many relationship.

The disadvantage of a nullable foreign key is being unable to model the relationship as many-to-many, if that is what you are trying to accomplish.


Based on your edit to the question, you are effectively splitting the student table into two tables with the same key. I generally see this on tables that have way too many fields, so someone splits them into two to be more manageable (I call it putting lipstick on a pig).

By splitting the student table, you are making the second table optional because a record need not exist in the second table. Which is very similar to a field not needing to be set because it can be null.

If you want a one-to-many relationship, you are far better off using a single table and allowing the school ID to be null in the student table. There is no reason to avoid nulls in fields, even for a foreign key. That signifies that the foreign relationship is optional: developers and DBAs understand that clearly, and the underlying database engine certainly should work fine.

If you are concerned about joins, do not worry. There are well-define semantics for how joins work with null fields. By using a single table, you can join two tables instead of three.

  • So if I am modelling a one-to-many relationship (with optional participation on the :1 side), I should use a foreign key despite the fact that it can have NULL values? – Tom Nov 3 '16 at 17:18
  • 1
    @Tom yes, that is exactly how to model it. While technically possible to use a join table, the data model allows many to many so you will need triggers and database logic to prevent that. You are better off by restricting the relationship in a way that it is impossible to add incorrect data. – user22815 Nov 3 '16 at 17:20
  • 1
    I edited to my question. I only made student_id a primary key in the School_has_Student table, which kept the relationship as one-to-many. What drawbacks does this method have over using a foreign key? – Tom Nov 3 '16 at 17:42
  • @Tom I edited my answer. – user22815 Nov 3 '16 at 18:01
7

You wrote in a comment above:

the book "Fundamentals of Database Systems" [...] says [...] that it is recommended to use an intersection table if there are a lot of NULL values in the foreign key column (for example: if 98% of employees don't manage a department)

When there are a lot of NULL values in the foreign key column, your programs will have to deal with this mostly empty column for each and every record they process. The column will probably occupy some disk space even though in 98% of all cases its empty, querying the relationship means querying that column which gives you more network traffic, and if you are using an ORM which generates you classes from your tables, your programs will also need more space at the client side than necessary. Using an intersection table avoids this, there will be only link records necessary where the equivalent foreign key would not be NULL otherwise.

Opposed to that, if you have not just a few NULL values, lets say 50% or more relations are not NULL, using an intersection table gives you the opposite effect - more disk space, higher complexity resulting in more network traffic etc.

So using an intersection table is just a form of optimization, only sensible for a specific case, and especially nowadays, where disk space and memory became cheaper, much less frequently needed. Note that "Fundamentals of Database Systems" was originally written more than 20 years ago (I found a reference to the second edition from 1994), and I guess that recommendation was already in there at that time. Before 1994, space optimization was probably much more important than today, since mass storage was still more expensive and computers and networks were much slower than today.

As a side note to a picky comment: the above statement is just trying to anticipate what the author of "Fundamentals of Database Systems" had in mind with his recommendation, I guess he was making a rough, general statement, valid for most systems. In some databases there are other possible optimizations like "sparse columns" which make the use of an intersection table even more obsolete.

So don't get that recommendation wrong. The book does not tell you to prefer intersection tables for {0,1}:n relationships in general, or - as you wrote - that this is the "correct way". Use optimizations like this which will make your programs more complicated only when you really need them.

  • You're assuming a lot about the implementation of the database, especially considering the OP didn't mention a specific one. It's more than likely that the database is smart enough to use only a small amount of space for sparse columns. – gardenhead Nov 4 '16 at 18:28
  • @gardenhead: what makes you believe that this is "more than likely"? – Doc Brown Nov 4 '16 at 23:56
  • The fact that databases have been around for decades and are highly optimized as they are a critical component of most infrastructures. – gardenhead Nov 5 '16 at 1:33
  • @gardenhead: sounds to me you are making much heaver unjustified assumptions than me. Nevertheless, see my edit. – Doc Brown Nov 5 '16 at 9:54
2

Conceptual model will look like this, which is very unorthodox to say the less:

enter image description here

Physical model will look like this, which is confusing to say the less (people will think it's M:M unless they see closely):

enter image description here

My suggestion:

If you have like, many columns (FK or otherwise), that do not apply to most students, separate the tables into role tables with 1:1 rels. But that's not because they are FK, it's because the columns don't apply to most rows.

Otherwise, nullable FK are a normal part of a database and join tables are usually for M:M rels.

Common uses of 1:1 rels are for role tables having columns that apply only if the entity is of a certain type, and extracting BLOB columns for performance or storage considerations. Avoding null values in FKs is not one common usage for that.

enter image description here

2

In addition to other answers I would like to point out that a null value for the foreign key is ambiguous. Does it mean:

1) The student's school (if any) is unknown (this is the standard meaning of 'null' - value is unknown)

2) It is known whether or not the student has a school, and they have none

If you use the standard meaning of null, how would you represent "student has no school" in your foreign key model. In that case, you'd probably have to create a "no school" entry, with it's own id in the school table. (Not ideal)

  • 2
    The book "Fundamentals of Database Systems" mentions that there are 3 interpretations for NULL, it can mean: 1) Unknown value. 2) Unavailable or withheld value. 3) Not applicable attribute (I think this interpretation means that you can specify a NULL for a foreign key). – Tom Nov 4 '16 at 21:43
  • 1
    That's a useful list but the semantics of null (or any value really) are user-definable. Ie it can mean whatever the designer says it means, not limited to that list. The issue is how to distinguish different meanings when more than one might be required (or even saved unintentionally) – Brad Thomas Nov 4 '16 at 21:56
  • So are you suggesting that I should create an intersection table instead of using a nullable foreign key? – Tom Nov 4 '16 at 22:44
  • @Tom Yes, I believe that is better in this case – Brad Thomas Nov 8 '16 at 23:03
  • @BradThomas - to avoid the same ambiguity when using an intersection table, would you represent case 2 (it is known that the student has no school) by a record in the intersection table with a NULL School_ID? – andrew Feb 20 '18 at 4:36
1

Database tables have this nice thing called constraints. So it's very easy to make in intersection table that allows only 1 of each student to appear in the table but many schools in that table. Effectively giving you a

Theory is nice but in the end you are going to model your database after the questions you're asking.

If you want to question often with the question: "which students are in my school" do you really want to query the entire students table or have an easy intersection table.

In databases: optimize for the questions you ask.

0

There is an use case where using a third table may actually make sense. The example may seem purely hypothetical, but I hope it illustrates my point well. Let's assume you add more columns to the students table and at some point, you decide to enforce uniqueness on the records via composite index on several columns. It's very likely that you'll have to include the school_id column as well, and here things begin to get messy. Due to the way SQL was designed, inserting several identical records where school_id is NULL will be possible. It makes perfect sense from a technical perspective, but is counterintuitive and may lead to unexpected results. On the other hand, enforcing uniqueness on the intersection table is easy.

I had to model such an "optional" relationship recently, where the requirement for an uniqueness constraint was due to a timestamp column. Leaving the nullable foreign key in the table suddenly lead to the possibility of inserting records with the same timestamp (let's assume it's a default one, set on records that haven't been audited/approved yet) - and the only way out was to remove nullable column.

So as you can see, it's a fairly specific case, and as others noted, most times you'd be perfectly ok with all the NULL values. It really depends on the specific requirements of your model.

0

In addition to the many good suggestions already submitted, personally I'm not a fan of foreign keys unless they are truly necessary. First there is the M:M relationship that you are referencing. Plus, calling a foreign key, and thereby pulling that table data into your queries, introduces more complexity and depending on table size, slower performance. As others have said, nullable FK fields can be non-supported and can create data integrity issues.

If you are defining a state where the student school is unknown or empty, the NULL will not differentiate those conditions. (again we're back to data integrity.) The role table suggestion by Tulains is elegant and allows for null values cleanly.

protected by gnat Oct 18 '17 at 11:53

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.