Category: Database

A lot of people have problem with relational databases and how to define relationships between two tables.

This is a guest post by Dave from saturnasoftware.com where he teaches us how to easily recognize the correct relationship:

General Rule and One-to-Many Relationship

Back when I taught database design at university I had a LOT of students who struggled with One-to-One vs. One-to-Many vs. Many-to-Many. Part of the problem is that English can be a bit confusing with plural forms: Person/People, Mouse/Mice, etc. Another part of the problem is that verbs do not always have exact inverses so we end up saying ‘Queen Elizabeth RULES England’ but then have to say the rather awkward ‘England IS RULED BY Queen Elizabeth’. There are so many slight variations in English that it gets confusing. Here’s a very simple way to ALWAYS get it right:

If you want to figure out the relationship between X and Y, complete the follow two statements:

• Each X GOES WITH, at most, ____ Y
• Each Y GOES WITH, at most, ____ X

Let’s assume we have Person and Country tables. For the sake of this example we assume that every person lives in only one country. So, for this specific case, we have the context of ‘where a person lives’:

• Each Person GOES WITH, at most, ONE Country
• Each Country GOES WITH, at most, MANY Person(s)

Now, keep the last two words of each statement…

• We have [ONE Country] to [MANY Person]

A One-to-Many relationship is always “owned” by the MANY side, which is to say that the Foreign Key goes in the table for the MANY side (we put the Country identifier in the Person table).

Many-to-Many Relationship

A Many-to-Many relationship (use the same two statements to figure this out) is special; it requires a third table. Each of the two related items has a One-to-Many relationship with this new third table, so the third table contains TWO Foreign Keys, one for each related table.

So, in the context of ‘places a person has visited’, we have:

• Each Person GOES WITH, at most, MANY Country(ies)
• Each Country GOES WITH, at most, MANY Person(s)

Incidentally, this ‘third table’ is conventionally named for the other two tables, in alphabetical order: CountryPerson or Country_Person, but can be named anything (e.g. Visited). Naming it after the other two tables makes it easy to figure out what the purpose of the table is; alphabetical is just arbitrary.

One-to-One Relationship

A One-to-One relationship (again, same two statements) is super easy – it does not matter which of the two tables gets the Foreign Key. Almost always one or the other of the items logically “owns” the other one… but it technically does not matter.

The most common kind of One-to-One relationship, in my experience, is an “IS-A” relationship – like “A Customer IS-A User” or “An iPhone IS-A Mobile Device.” A trick that I use for IS-A type relationships is to use the same Primary Key(identifier) in both tables. That way either Primary Key can serve as the Foreign Key to the other table.

But One-to-One relationships are not ALWAYS IS-A relationships… for example…

In the context of “place that this person rules (as in King/Queen)”, we have:

• Each Person GOES WITH, at most, ONE Country
• Each Country GOES WITH, at most, ONE Person

Here we could put the Person identifier in the Country table OR we could put the Country Identifier in the Person table. Since there will likely be fewer Countries than Persons, I’d put the Person identifier in the Country table because it will result in less data to store/handle.

There you have it, the (very) short version of my ‘what kind of relationship is this?’ lecture.
Hope it helps someone.