26

I have two simple tables "items" and "orders". For the sake of simplicity lets assume that one item can only be in one order or one order can only contain one item.

Now as this can be implemented using simple one to one relationship I can do following:

I can add the primary key of the orders table to the items table like below

//Table Items
item_id, item_name, order_id
1,        shoes,    1
2,        watch,    2

//Table Orders
order_id, customer
1,        James
2,        Rick

or I can add the primary key of the items table to the orders table like below

//Table Items
    item_id, item_name
    1,        shoes
    2,        watch

//Table Orders
order_id, customer, item_id
1,        James,    1   
2,        Rick,     2

Which one is correct and why? Are there any guide lines to decide which key goes where? Sure common sense will work in simple examples as above but in complex examples how do we decide?

Jay Bhatt
  • 5,601
  • 5
  • 40
  • 62

1 Answers1

29

One-to-One relationships should be generally merged simply into one table. If there aren't any contradictions, the One-to-One relationship might be a sign of an unconsidered decision.

And If You really want to use this kind of relationship, it's totally up to You where to place FK. You might want to take optionality into consideration when applying FK. However, in MySQL, it still won't be a true One-to-One relationship because deferred keys are not supported there.

Grzegorz Piwowarek
  • 13,172
  • 8
  • 62
  • 93
  • Agree. But then they won't be one to one relationships they will become Self Referencing Relationships. – Jay Bhatt May 31 '13 at 18:23
  • @JayBhatt If I understood You correctly I don't see a Self Referencing Relationship here. Are You asking about general principle or just about a solution to this particular problem? – Grzegorz Piwowarek May 31 '13 at 18:28
  • I am asking about general concepts. – Jay Bhatt May 31 '13 at 18:30
  • @JayBhatt Where do You see a self referencing relationship in the above approach? SRR creates hierarchies and I don't see any there. – Grzegorz Piwowarek May 31 '13 at 18:37
  • I think we are moving out of the context here... I am simply looking for guide lines on deciding on the foreign key. One example can be that "If table B is a property of table A (address is a property of customers object) then then the primary key of B becomes foreign key of A. – Jay Bhatt May 31 '13 at 18:46
  • @JayBhatt Ok, here goes the answer: It's totally up to You where to put FK. You might want to consider optionality as something that could help You decide. Anyway, You chose a very bad example for representing a 1-1 relationship. – Grzegorz Piwowarek May 31 '13 at 19:09
  • I don't agree to this. I.e. If one mobile phone is linked to one part the which one makes more sense, adding the foreign key of part to mobile phone or adding the id of mobile phone to part? Clearly it doesn't work both ways. – Jay Bhatt May 31 '13 at 19:31
  • @JayBhatt You asked about general concept using a very bad example and I gave You general answer. In the last example the logical choice is to put FK in the mobile phone part but as long as You're using **one-to-one** approach it doesn't matter and it's a bad choice. One-to-many relation is a much better choice. – Grzegorz Piwowarek May 31 '13 at 19:37
  • 1
    @JayBhatt You simply can't have "one to one" relationship with two tables without deferred foreign keys (which are not supported by MySQL). The best you can have is "one to zero or one" - the placement of FK determines which side is which. If you want the real "one to one", you'll have to do it in one table. – Branko Dimitrijevic May 31 '13 at 20:48
  • 9
    Some one-to-one relationships are of the IS-A variety, unlike the example given in this question. IS-A relationships are often examples of a pattern called class/subclass in object modeling, and called generalization/specialization in ER modeling. In this situation it's often useful to split the tables as in [Tag:class-table-inheritance] – Walter Mitty Jun 04 '13 at 23:02
  • 2
    We had a similar problem Our problem was Transaction and Location have one-to-one relationship. Location contains information of where the Transaction took place. After further analysis we came to conclusion that Location was related to Transaction and does not exist independently. In a way this is like a child entity. Hence, we used a Foreign Key of Transaction in the Location table. – Shashi Jeevan M. P. Jan 04 '18 at 14:53
  • @ShashiJeevanM.P. nice reasoning, i'll go with that too. – neric Sep 14 '19 at 15:45