In RDBMS, when a relation is one-to-one, does it help if both records point to each other, or is one pointing already enough (no need both pointing)?

Question

I was reading a book and it talks about a User has some more UserDetail, and so the UserDetail will have a user_id pointing back to the Users table. I kind of forgot that, does it help at all to has a field in Users table to have a user_detail_id to point at the UserDetail record?

This is so in Ruby on Rails too, that the Users table doesn't have a user_detail_id, but the UserDetail table has a user_id. And the User model use has_one :user_detail, while the UserDetail model use belongs_to :user.

It kind of makes sense that if it is a one-to-many relationship, then at the "Many" side, we have an foreign_id pointing back to the "One" side, but the "One" side doesn't need to point to the "Many" side, so it looks like a one-to-one doesn't need to have it pointing both ways too, as one side is enough.

Answer 1

It's not a good idea, what would you do if you wanted to delete a row? You'd need to disable the constraint!

Answer 2

That's correct, in terms of foreign keys the relationships only need to be set up for one direction. Note that ActiveRecord associations in Rails let you traverse the association from either end though. For example:

@user.user_detail

# Or
@user_detail.user

Answer 3

In the case of a one-to-one, you can think of it exactly as a one-to-many except that the "many" part is always going to be "one". With that being said, having the foreign key on the "many" side is going to be good enough, and there is no need for any double pointing. It's as simple as that.

Answer 4

at the "Many" side, we have an foreign_id pointing back to the "One" side, but the "One" side doesn't need to point to the "Many" side, so it looks like a one-to-one doesn't need to have it pointing both ways too, as one side is enough.

You never implement foreign keys on both sides of the relationship--you'd never be able to insert data without disabling the constraints because the constraints would require the information to exist in both tables at the same time. One of the two tables has to be considered the parent entity, where the child entity will rely on the parent_id for referential integrity...

But I think you're getting the idea why you don't generally model tables in a one-to-one relationship. It creates pointless overhead, by duplicating the primary key as the foreign key (at a minimum) and includes the foreign key constraint as additional baggage. One-to-one tables are resorted to in order to address performance issues; unless there is a performance issue, it's premature optimization...