CODEWITHSUNDEEP
sqldatabasedatabase-designdatabase-schema
Treeline
Treeline

Reputation: 485

How to avoid circular dependency here

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

The intuitive solution:

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.

Upvotes: 0

Views: 455

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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 LawnmowerLawns 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).

Upvotes: 1

Related Questions