CODEWITHSUNDEEP
database-design
reggaeguitar
reggaeguitar

Reputation: 1804

Circular Relationship in Database Design

I have a database with 4 tables (there are more, but only 4 are causing the problem). The rules are: a Model has 1 to many tasks. A Task has one to many Sets. A SuperSet has one to many Sets and a Model has one to many SuperSets. A SuperSet is basically just a list of sets. The design has led to a circular looking dependency graph like the following:

Models <--------------------------- SuperSets
   ^                                    ^
   |                                    |
   |                                    |
Tasks  <---------------------------- Sets

I've read this http://www.codeproject.com/Articles/38655/Prevent-Circular-References-in-Database-Design , but I can't get rid of any of the tables, and the second example is a different case than mine. I've read that circular relationships aren't desirable but I don't really see any way around this one. Is this even a problem, and if so how can I fix it?

Upvotes: 3

Views: 17464

Answers (2)

WW.
WW.

Reputation: 24271

As has been said, there is no circular reference in your model. The arrows do not go around in a circle.

Perhaps you're worried that Sets can reach it's grandparent Models through two different parents. This is a legitimate concern which can be resolved by ensuring that the primary key of Models is part of the primary key of the other three tables.

With foreign keys in place, this ensures that each row in Sets belongs to exactly one model.

So the design would be as follows:

  • Model table has primary key of ModelId.
  • Tasks table has a primary key of ModelId and something else, say TaskNumber. ModelId is also a foreign key to Models.
  • SuperSets table has a primary key of ModelId and something else, say SuperSetNumber. ModelId is also a foreign key to Models.
  • Sets has a primary key of ModelId, TaskNumber, and SuperSetNumber. It has foreign keys to be Tasks and SuperSets.

In this design because the single column Sets.ModelId is part of two foreign keys, the data integrity is enforced.

Upvotes: 15

Szymon
Szymon

Reputation: 43023

Your design is not circular.

There's Models at the top level with 2 child tables Tasks and Supersets. Then there's Sets on the lowest level. None of the levels go back to the higher level table.

This is fine and won't cause any problems.

Upvotes: 6

Related Questions