Are circular references acceptable in database?

Question

When are circular references acceptable in database?

Theoretical and practical, any help is appreciated.

Answer 1

Circular references should be avoided like the plague. It is possible to set up two way relationships, or even relationships to yourself (if you were a table that is), but a circular dependency is just asking for trouble.

Answer 2

I have seen circular references done for performance reasons. It looks ugly though, and the performance might be negligible.

Example: some bulletin boards (I think phpBB does this) have a lastpostid in the category table that is a shortcut to the last post in the thread.

This creates a circle, where the last post has a FK to the category table and the category table has a FK back to the last post.

Like I said, I don't really like it, but I've seen it done.

Answer 3

rarely i run across a 1:1 relationship that is necessary and imposes a circular relationship

note that the foreign-key fields in such a relationship must be nullable, otherwise you can never delete rows from the tables

Answer 4

If i heard a good reason why - sure.

Answer 5

I guess it isn't a problem if you are using a write only database. If you plan on using the RUD part of CRUD, you'll likely run into (usually avoidable) complex issues in dealing with them.

Answer 6

It is technically possible to do, but it can cause all sorts of problems when deleting records as it generates chicken-and-egg problems. These problems often take drastic action like manually dropping the FK's and deleting the offending items to resolve.

If you have a relationship like:

create table foo_master (
       foo_master_id int not null primary key
      ,current_foo_id int
)


create table foo_detail (
       foo_detail_id int not null primary key
       foo_master_id int not null
)

alter table foo_master
  add constraint fk_foo_current_detail
      foreign key (current_foo_id)
      references foo_detail

alter table foo_detail
  add constraint fk_foo_master
      foreign key (foo_master_id)
      references foo_master

Then deleting a record can cause such a chicken-and-agg problem due to the circular dependencies.

A better schema for this looks like:

create table foo_master (
       foo_master_id int not null primary key
)


create table foo_detail (
       foo_detail_id int not null primary key
       foo_master_id int not null
       is_current char (1)
)

alter table foo_detail
  add constraint fk_foo_master
      foreign key (foo_master_id)
      references foo_master

This means that the relationship is non-cyclic and the 'current' foo_detail record can still be identified.

Answer 7

Records which point to other records are useful in a database. Sometimes these records form a cycle. This might still be useful. The only real annoyance in practice is avoiding violating the constraints.

For example, if you have a user and transaction table, the user might have a pointer to his last transaction. You need to insert the transaction first, then update the last_transaction_id to the correct value. While both these records exist you can't erase them, because the user.last_transaction_id points to transaction.id and transaction.user_id points to user.id. This implies that a user with no transactions has a null last_transaction_id. It also means that you have to null that field before you can delete the transaction.

Managing these foreign key constraints is a pain but it certainly is possible. There may be problems that arise if you add constraints to the database later which introduce new circular dependencies. You have to be careful in this situation. However, as long as one of the records in the cycle has a nullable foreign-key field, the cycle can be broken and the records can be deleted. Updates are not usually a problem as long as you insert the records in the right order.

Answer 8

One of the latest additions to the Oracle hierarchical query syntax - the NOCYCLE keyword - was made for expressly this purpose - to deal with circular references in the data. I don't see anything wrong with it, and have had to deal with this kind of model before. It's not too difficult, especially in Oracle which supports deferrable constraints.