What is the correct way to model one-to-one, one-to-many, many-to-many? Where do foreign keys go? What criteria to use? How to decide? I see these questions repeated on StackOverflow time and again.
The particular implementation should be based on logic. In most cases, verbalizing the constraint in natural language reveals the correct solution.
The following example uses two entities: thing T, and category C. Defining relationship with four most common multiplicities results in 4x4=16 possible constraints.
Term Multiplicity
------------------------------------
is in exactly one [1]
is in at most one [0,1]
is in at least one [1,*]
may be in more than one [0,*]
Easy
The following 11 cases can be declaratively defined in SQL using standard constraints (FK, PK, AK). No need for triggers, deferred constraints, stored procedures, nor special application code.
T |-[1]------------[1]-| C
1. Each thing is in exactly one category;
for each category: exactly one thing is in that category.
thing {T, C}
PK {T}
AK {C}
T |-[0,1]----------[1]-| C
2. Each thing is in exactly one category;
for each category: at most one thing is in that category.
thing {T, C}
PK {T}
AK {C}
FK {C} REFERENCES category {C}
category {C}
PK {C}
T |-[1]----------[0,1]-| C
3. Each thing is in at most one category;
for each category: exactly one thing is in that category.
thing {T}
PK {T}
category {C, T}
PK {C}
AK {T}
FK {T} REFERENCES thing {T}
T |-[0,*]----------[1]-| C
4. Each thing is in exactly one category;
for each category: more than one thing may be in that category.
thing {T, C}
PK {T}
FK {C} REFERENCES category {C}
category {C}
PK {C}
T |-[1]----------[0,*]-| C
5. Each thing may be in more than one category;
for each category: exactly one thing is in that category.
thing {T}
PK {T}
category {C, T}
PK {C}
FK {T} REFERENCES thing {T}
T |-[0,*]--------[0,*]-| C
6. Each thing may be in more than one category;
for each category: more than one thing may be in that category.
thing {T}
PK {T}
category {C}
PK {C}
thing_category {T, C}
PK {T, C}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
T |-[0,*]--------[0,1]-| C
7. Each thing is in at most one category;
for each category: more than one thing may be in that category.
thing {T}
PK {T}
category {C}
PK {C}
thing_category {T, C}
PK {T}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
T |-[0,1]--------[0,*]-| C
8. Each thing may be in more than one category;
for each category: at most one thing is in that category.
thing {T}
PK {T}
category {C}
PK {C}
thing_category {T, C}
PK {C}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
T |-[0,1]--------[0,1]-| C
9. Each thing is in at most one category;
for each category: at most one thing is in that category.
thing {T}
PK {T}
category {C}
PK {C}
thing_category {T, C}
PK {T}
AK {C}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
T |-[1,*]--------[0,*]-| C
10. Each thing may be in more than one category;
for each category: at least one thing is in that category.
thing {T}
PK {T}
category {C, T}
PK {C}
FK {T} REFERENCES thing {T}
thing_cat {T, C}
PK {T, C}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
VIEW thing_category {T, C}
AS
category {T, C}
UNION
thing_cat {T, C}
T |-[0,*]--------[1,*]-| C
11. Each thing is in at least one category;
for each category: more than one thing may be in that category.
thing {T, C}
PK {T}
FK {C} REFERENCES category {C}
category {C}
PK {C}
thing_cat {T, C}
PK {T, C}
FK1 {T} REFERENCES thing {T}
FK2 {C} REFERENCES category {C}
VIEW thing_category {T, C}
AS
thing {T, C}
UNION
thing_cat {T, C}
Not Easy
Cases from 12 to 16 require use of triggers, stored procedures, deferred constraints, or application code to enforce the constraint.
12. T |-[1,*]----------[1]-| C
Each thing is in exactly one category;
for each category: at least one thing is in that category.
13. T |-[1]----------[1,*]-| C
Each thing is in at least one category;
for each category: exactly one thing is in that category.
14. T |-[1,*]--------[0,1]-| C
Each thing is in at most one category;
for each category: at least one thing is in that category.
15. T |-[0,1]--------[1,*]-| C
Each thing is in at least one category;
for each category: at most one thing is in that category.
16. T |-[1,*]--------[1,*]-| C
Each thing is in at least one category;
for each category: at least one thing is in that category.
The Problem
The main problem is lack of assertions (DB-wide constraints) in main SQL implementations. SQL standard actually defines them (CREATE ASSERTION), but no luck yet. Hence, not every business constraint can be elegantly defined in SQL. Often some creativity, compromise, and awkwardness is required.
Note:
All attributes (columns) NOT NULL
PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key