CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
ganeshran
ganeshran

Reputation: 3512

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 table.

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?

Upvotes: 3

Views: 1619

Answers (5)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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.

Upvotes: 3

reaanb
reaanb

Reputation: 10064

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.

Upvotes: 0

kevchadders
kevchadders

Reputation: 8335

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.

Upvotes: 1

DGH
DGH

Reputation: 11549

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.

Upvotes: 1

wizzardz
wizzardz

Reputation: 5874

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.

Upvotes: 7

Related Questions