One to one relation in SQL database

Question

I have two tables: "Person" and "Details". Person should have one to one relation with Details. I have two options:

Write DetailsId in Details table and make it FK to Details.Id(PK);
Make Details.PersonId as PK and FK at the same time to Person.Id;

In both cases relationship is one to one. Can you explain which one is better and in what cases?

EDIT:

I'm using entity framework and want to get Detail like this: Person.Detail, not Person.Details.First()

you forget 3th case: Write PersonId in Details table and make it FK to Person.Id(PK) ;) — dani herrera, Oct 31 '12 at 11:05
@danihp This will be one to many relationship. One person may have many Details in your case — karaxuna, Oct 31 '12 at 11:19
@danihp EF sees it as one to many relationship, sorry i had to mention about EF earlier — karaxuna, Oct 31 '12 at 11:27
see http://stackoverflow.com/questions/7644156/implementing-one-to-zero-or-one-relation-in-sql-server — techBeginner, Oct 31 '12 at 11:42

score 3 · Answer 1 · answered Oct 31 '12 at 11:10

Make two tables "Person" and "Details". In "Details" table, take a field "person_id" which will be your FK that will refer to id field(PK) of the "Person" table. That is the better solution. Because "Details" table will require more operations like update etc. than "Person" table. Therefore make "Person" table as Master & "Details" as slave.

score 2 · Answer 2 · edited May 23 '17 at 10:32

2

For a truly 1:1 relationship, your default solution should be to merge the tables together, unless there are specific reasons against it.

But I suspect you are actually talking about 1:0..1, in which case (2) will model what you need.

I don't understand the (1) - what's the purpose of the self-referencing FK there? Did you actually mean a FK in Person referencing Details?

edited May 23 '17 at 10:32

Community

1
1

answered Oct 31 '12 at 19:04

Branko Dimitrijevic

50,809
10
93
167

score 2 · Answer 3 · answered Nov 01 '12 at 09:55

Are there specialized subclasses of "persons"? In that case, you may want to have more than two tables, with a different subclass table for each subclass. See the tag "[Class Table Inheritance]" here in SO. You also should look up the tag "[Shared-Primary-Key]". It works better than having a separate FK and PK for the subclass table.

You also need to consider the benefits and the costs of choosing a single table solution.

One to one relation in SQL database

3 Answers3