Treeline Treeline - 7 months ago 12
SQL Question

How to avoid circular dependency here

I have two tables; to make the example easy, let's call them

lawnmower
and
lawn
.


  • The lawnmower is always mowing a lawn.

  • Multiple lawnmowers can be mowing the same lawn.

  • A lawn has a single lawnmower which is responsible for that lawn.

  • A lawnmower can however be responsible for 3 lawns at the same
    time.



The intuitive solution:


  • Let lawnmower reference the lawn it is mowing

  • Let lawn reference the responsible lawnmower



This gives a circular dependency. What is the best solution to avoid this? I'm currently considering a
responsiblefor
table with foreign keys to a lawn and a lawnmower. This however enables multiple lawnmowers to be responsible for the same lawn, which was not possible before.

Answer

First, you want a junction table because this is fundamentally an n-m relationship. This table would have one row per lawnmower and per lawn (I would called it LawnmoverLawns or something like that).

Now to handle the bullet points:

  • The first one requires a row in this table for every lawnmower. This can be handled with a trigger in most databases. There are probably other solutions as well.
  • The second is handled by the junction table.
  • The third is handled by having a flag in the table to represent the "primary" lawnmower for each lawn. This can be enforced via a trigger or other mechanisms.
  • The fourth requires a trigger (counts on a relationship usually require triggers).