Is it a good practise to have circular reference in two DB tables

In our DB we have two tables A, B with primary keys A_id and B_id.

Is it a considered a good practice to have B_id as foreign key in table A and A_id as foreign key in table B. This would allow us to have many-to-many relationship in the 开发者_如何学Ctable.

An alternative would be to have a third bridge table consisting of just two columns A_id and B_id.

Which one do you think is a good practice?

I think a bridge table would be ideal for implementing many to many relationship between two tables.And it is not a good practice to have a circular reference between tables.

Consider following scenario

TableA  TableB
A       1
B       2

If you want to crosslink this, the least you need to do without creating a third table is duplicating every row in one of the two tables. I doubt you'll find many DBA's willing to model their tables like that.

TableA
A, 1  
A, 2   
B, 1
B, 2

TableB
1
2   

A third bridge table really is your only good option here.

It depends on the relationship between A and B, whether it is one-to-one, one-to-many, or many-to-many. In general, though, circular references are bad simply because they increase the amount of maintenance you have to do to keep the two tables in sync.

As wizzardz mentioned and espically with DBMSs, i'd try to avoid circular references.

It has the potential of causing you a great deal of problems. Also if others will be working with that design, you'll have to nail down the documentation for it as they could end up going round in circles trying to work it out.

What you call a bridge table is the normalization of a join dependency, and it's supported by good theory. You should not be recording the same fact in multiple locations.