2

What is the best way to design one-to-many relationship with nullable FK?

I have two tables:

TableA:
  Id PK
  Name 

TableB:
  Id PK
  TableARef FK
  Name

TableB has reference to TableA but this reference can be null. What is the best way to design that? I want to avoid nulls in my database (or maybe I shouldn't). It is a good practise to add join table to one-to-many relationship? Like this:

TableA_TableB:
  Id PK
  TableARef FK
  TableBRef FK

I know that in many-to-many is a good solution but I couldn't find enough data about this solution in one-to-many relationship.

  • "I want to avoid NULLs" why? If you don't have a value for something a NULL value is the best way to store that. There is nothing bad about NULL values. Avoiding them is not a bad idea. Avoiding them by all costs is a bad idea though. – a_horse_with_no_name Jun 27 at 10:49
  • 1
    To answer the question: "What is the best way to design one-to-many relationship with nullable FK" - store it in a nullable column. – a_horse_with_no_name Jun 27 at 10:50
  • 1
    To play the devil's advocate, there is nothing wrong with your TableA_TableB idea either. You only need a UNIQUE constraint on (TableBRef) to ensure the one-to-many. And you can drop that Id column, entirely useless. – ypercubeᵀᴹ Jun 27 at 11:03
  • Of interest. – MDCCL Jun 27 at 17:26
  • Thank You for comments. They are very useful. – Marcin Korwek Jun 28 at 6:59
1

I would not model a one->many relationship as a many<->many one, with a junction table as you describe, because you are allowing more in your data model than the system it is modelling. You will need to add extra constraints, either in the DB if possible or other application layers, to enforce the more limited relationship and/or add code to detect and deal with breaches of this constraint that happen due to bugs.

Is there a particular reason you want to disallow NULLs for a value that seems to naturally call for them?

If so then you could create "partial junction table" or "extension table" (I'm making that terminology up, I don't know off the top of my head if this is a pattern that has a commonly accepted name):

TableA_TableB:
TableARef FK, NOT NULL
TableBRef FK, PK

this way the FK ensures that you have a one->many relationship as each ID from TableB can only appear once while rows from table A can be referred to by many entities in TableB. The empty condition then becomes that the row does not exist. This avoids the NULL needing to be "stored" but you now have an extra table to join in every time you need to use this field.

If you want to have a surrogate key as your PK, as in the example in the question, then the definition becomes:

TableA_TableB:
Id        PK
TableARef FK, NOT NULL
TableBRef FK, UNIQUE

though that would be wasteful IMO unless you think that you might in later revisions change the relationship to be many<->many where it is valid for the same two entities can relate to each other more than once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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