Hi there,

After designing my database i have realised that the architecture of my database tables/structure contains a cycle.

For example i have 5 tables where it links like:

- 1 > 2 > 3 > 4 > 5
- 1 > 5

As you can visualise table one is like the start and 5 is like the end but now it contains a cycle.

is this an element of good database design?

many thanks in advance

You said 1 > 5. That's not a cycle. 5 > 1, would make it a cycle.

If a record has to be in 1 to place a record in 2, and one has to be in 2 to be in 3, and one has to be in 3 to be in 1, that's a cycle.

That can be annoying since you have to populate all the tables simultaneously for the transaction to go through.

But if a record has to be in 1 to be in 2, and one has to be in 2 to be in 3, and one has to be in 1 to be in 3, that's not the same.

In that situation, you can insert a record into 1 without any problem.

People act like cycles are the end of the world, but seriously, all it means is that the normal INSERT statement doesn't automatically work. It's not all that hard to work around, depends on the DBMS, but you generally just disable integrity checks temporarily, and once you figure it out, you can just package that into a stored procedure.

Updating works like usual. You'll definitely want to check how deletes are cascaded. Just write out a few examples on paper and then test to see that the DBMS is doing what you expect.

In pure data modelling terms there is nothing wrong with a cycle if it is an accurate model of your domain of discourse. Cycles of dependencies are not uncommon in reality.

Unfortunately SQL makes it almost impossible to implement most such models if all the dependencies are mandatory ones. If you are using a SQL DBMS then for practical purposes you will usually have to compromise, either by removing the cycle altogether or by allowing some constraints to be optional or broken some of the time (a SQL feature called deferred constraint checking). This is purely a limitation of SQL however, it doesn't mean you did anything wrong.

thanks.

I now understand better. i'll take your advices on board.