Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    24

    Question My EER has a cycle? good - bad ?

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  4. #4
    Join Date
    Nov 2009
    Posts
    24
    thanks.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •