I'll be using an Ecommerce application in these examples.
PK stands for Primary Key, and
FK stands for Foreign Key.
Use a foreign key on both sides of the relationship.
One customer can have one address.
|id (PK)||first_name||last_name||address_id (FK)|
|id (PK)||street_address||city||zipcode||customer_id (FK)|
You must also put a unique constraint on the foreign key columns to prevent multiple rows in the child table (
address) from relating to the same row in the referenced table (
Use a foreign key on the "many" (child) side of the relationship linking back to the "one" (parent) side.
One customer can have many orders.
customer table (parent):
order table (child):
|id (PK)||delivery_date||customer_id (FK)|
One-to-many (Lookup table)
When the child is just one value it can be stored directly in the parent table as an attribute, such as customer membership type.
But since there several set values to choose from, it would be better to put this information in a separate table called a lookup table, so that if a particular membership was renamed only one value in the lookup table would have to be updated.
|id (PK)||first_name||last_name||membership_id (FK)|
Use a junction table (also called an intermediary table, a bridge table or an associative entity).
Many customers can wishlist many products.
wishlist junction/intermediary/bridge table:
|id (PK)||customer_id (FK)||product_id (FK)|