In designing a schema that has a bidirectional relationship in the domain model, would the schema also have a bidirectional relationship?

Meaning, Table1 has a primary key and a foreign key pointing to the primary key of Table2. Does Table2 need a foreign key pointing to the primary key of Table1 as well?

Is the above scenario ever needed? In fact, is it ever valid? It seems to me that even in a bidirectional relationship, you would never have the above because it would be redundant. Is my reasoning correct?

Does the above violate any normalization principles?

Thanks.

In your example, Table 2 would not need to have a key pointing back to Table 1. However, such recursive relationships are possible, valid, and are used at times.

Thanks for replying.

If cases when you have a bidirectional relationship in your OO code (meaning one object has a reference to another, and that other object also has a reference to the first), then must you have 'bidirectionality' in your database as well? Or is it always possible to navigate both ways in your database by just having a 'unidirectional' relationship between two entities?

What are the advantages to designing with redundant foreign keys, or in what cases should I consider using them? If, through SQL, you can always get data from two entities when there is only one foreign key (rather than two) between those two entities, in what cases should your design incorporate the double-foreign key strategy?

Thanks.

There is no need to create foreign keys in both directions normally, and to do so would usually be wrong - unless there actually are two different relationships. If you create the same relationship both ways around then you are enforcing a mandatory 1:1 relationship, which may be valid but only rarely.

Thanks for your reply. I'm new to this so I can't picture how a bidirectional foreign key relationship enforces a mandatory 1:1 relationship.

Can't you still make it a 1:m relationship by simply adding, say, 5 more rows in one of the two tables that point to the primary key of a row in the other table?

vk101 said Let's see what happens when we do as vk101 suggests.

There is a table T1 with columns (p1 [primary Key], a1, a2, p2 [Foreign Key from table T2])

There is a second table T2 with columns (p2 [primary key], b1, b2, p1 [foreign key to Table T1])

Now let's try and create the bi-directional data as suggested.

Table T1 (p1, a1,a2, p2)
---------
Row 1: 101, 'S1', 'S2', 201

Table T2 (p2, b1, b2, p1)
---------
Row 1: (201, 'T1', 'T2', 101)

You say you want to create five rows in T2 that refer to T1's row with primary key 101. Let's create four more rows in T2.

Table T2:
----------
Row 2: (202, 'U1', 'U2', 101)
Row 3: (203, 'V1', 'V2', 101)
Row 4: (204, 'W1', 'W2', 101)
Row 5: (205, 'X1', 'X2', 101)

So far, no problem.

But remember, a bi-directional relation must exist in both tables!

Let's see what we have to do for table T1's data. We must add four more rows each with primary key 101 and foreign keys 202, 203, 204 and 205 respectively. This clearly is not possible since it violates the primary key constraint of table T1! Clearly, you can see that there can be only one row with primary key 101 in T1; and hence a bi-directional relationship may have only one value at each end.

Hence, Tony's statement that a bi-directional relationship mandates a 1:1 relationship.

Hope that helps.

Ravi

Very well demonstrated ... excellent answer!

I noticed two things about this, perhaps somebody could tell me whether my thoughts are correct:

1) This does not enforce 1:1 versus 1:0...couldn't somebody define a row in one table without a corresponding field in the other? Or am I wrong, in that there would have to be in order to validate the foreign key in the first table? (Can you have a foreign key that refers to a primary key in another table that does not yet exist, or has been deleted?)

2) There would technically be nothing wrong with somebody adding in the 4 extra rows that you added into the database...wouldn't the schema still be tehcnically correct? If so, then does this indicate a heavy responsibility on the users of the database (the code in the programming language accessing the database) to make sure that their SQL explicitly says something like WHERE pktable1=fktable2 AND pktable2=fktable1 to enforce the 1:1? Or is this automatically enforced based within the database itself?

Thanks for all the helpful answers thus far.

If you define a foreign key in table A references table B's primary key, and a foreign key in table B that references table A's primary key then you are saying: "Every A must have a corresponding B and every B must have a corresponding A."

Now this could be done in such a way as not to enforce a 1:1 relationship:

But, as I said earlier, this 2 constraints are not defining the same relationship - e.g. a(2) references b(1), but b(1) references a(1) not a(2).

To make them define the same relationship they would need to involve the same columns in both cases:
Now what happens if we insert rows into A without a corresponding row in B?
The only way to set up the data with this "bi-directional" relationship is that for every a.aid there is a corresponding b.bid and vice versa.

That is how it enforces a mandatory 1:1 relationship.

Come to think of it, didn't rajiravi just say all that and didn't you just say you understood?

So to deal with your questions, such as I understand them:

1) Yes it does, no they could not. Of course a foreign key can't point to a primary key that doesn't exist - how would that be different from not having a foreign key!?

2) Yes, it would impose a burden on the users - unless you created a view that performed the join, and told the users to use that.

My question would be: what do you think would be the point of defining these 2 Siamese Twin tables when a single table would achieve the same end so much more simply?

Wonderful answer, thanks a lot.

I'm not sure when this case might be more useful than just a single table (sometimes my questions may seem like I know almost nothing, which is pretty much the case ), but I've learned a lot about this whole topic from reading all these answers.

Thanks!