Jay Bhatt Jay Bhatt - 1 year ago 87
MySQL Question

Deciding on foreign key while implementing one to one relationship in MySQL

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?

Answer Source

One-to-One relationships should be generally merged simply into one table. If there aren't any contradictions, 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download