Within our DB we've two tables A, B with primary secrets A_id and B_id.
Could it be a considered a sensible practice to possess B_id as foreign type in table A along with a_id as foreign type in table B. This could let us have numerous-to-many relationship within the table.
An alternate is always to possess a third bridge table composed of just two posts A_id and B_id.
Which do you consider is a great practice?
I believe a bridge table could be well suited for applying many to a lot of relationship between two tables.Which is a bad practice to possess a circular reference between tables.
It is dependent around the relationship from a and B, whether it's one-to-one, one-to-many, or many-to-many. Generally, though, circular references can be harmful since they increase the quantity of maintenance you need to do to help keep the 2 tables synchronized.
Consider following scenario
TableA TableB A 1 B 2
If you wish to crosslink this, minimal you must do without developing a third table is
TableA A, 1 A, 2 B, 1 B, 2 TableB 1 2
Every row with a minimum of one table is copied for each many-to-many relationship.
I believe you will not find many DBA's prepared to model their tables like this.
Another bridge table is really your only wise decision here.
As wizzardz pointed out and espically with DBMSs, i'd avoid circular references.
It's the potential for leading to you a lot of problems. And if others is going to be dealing with that design, you will need to nail lower the documentation for this because they could finish up going round in circles attempting to settle your differences.
That which you call a bridge table may be the normalization of the join dependency, and it is based on good theory. You shouldn't be recording exactly the same fact in multiple locations.