Notes on database relationships

Quick post.

I've made mistakes while designing relational databases in the past, and most of them had to do with not understanding relationships well enough. I just happened to read someone else make a similar mistake on StackOverflow, and thought I'd write some quick notes on relationships here.

Every relationship has a principal end and a dependent end. The end with the foreign key is the dependent end.

There are kinds of relationships:

• One to One^[1] relationships (1:1)
• One to Many relationships (1:n)
• Many to Many relationships (n:n)

Before you create your next foreign key, make sure you follow these 3 rules:

1. One to One: Two primary keys
   The relationship must be between the primary keys (or candidate keys) of both the tables. That is, one of the primary key will be the foreign key as well.

2. One to Many: One primary key
   The relationship must be between a primary key (or candidate key) field and a non-unique field.

3. Many to Many: No primary key
   Both ends must be non-unique, one of them being a foreign key as well.
   You can always use an alternate key instead of the primary key.

So in short:

Relationship	Rule
1:1	unique : unique
1:n	unique : non-unique
n:n	non-unique : non-unique

If it weren't for relationships, relational databases wouldn't be relational :p
I couldn't resist 🙈

---

1. One to One relationships are actually One to Zero-or-One (1:0..1) because there's no way to force the relationship in practice. ↩︎