Relationships

Sorry guys, this is not a blog over dating advise. Rather, it is over database design relationships!

What is a relationship, you may ask? A relationship is the kind of connection an entity has with an attribute and the other way around as well as the connection between multiple tables. Here, lemme’ explain.

We categorize relationships into 3 groups:

  • one-to-one
  • one-to-many
  • many-to-many

These relationships are called binary relationships, because they explain relationships between two tables.

One to One Example

The best way to explain this is to look at an example: Let’s use the example of a real relationship…like a marriage! A husband and a wife… The husband has one wife and the wife has one husband. This is a 1 to 1 relationship because:

  • The husband can have one and only one wife.
  • The wife can have one and only one husband.

Here you can visually see the connection between the husband and the wife. This is probably the easiest example of a one to one relationship (ha! literally, a relationship!).

To humanize this a bit, we could think of the husband as Jim, and the wife as Jamie. If either Jim or Jamie decided to get a new wife/husband then there would be some serious problems!

One to one relationships across two tables are not super common. We are able to store one to one relationships in one table, but two tables is possible as well.

One to Many Example

Let’s look at an example of a marriage where the man can have multiple wives but the wife can only have one husband. Think of a powerful king (we’ll name him Fabio) who demands multiple wives. This example is a one to many relationship because:

  • The husband can have multiple wives.
  • Each wife can only have one husband.

Here we can see that the husband has 3 wives but the wives are not allowed to have multiple husbands.

King Fabio can be married to Sally, Sara, and La’Tanya, but if any of those girls marry another man then off with her head!

One to many relationships are often stored with two tables. This means that one row in TableA can have a relationship with multiple rows in TableB, but each of the rows in TableB can only be connected back to the single row in tableA.

Many to Many Example

If the husband in the relationship could have multiple wives and each wife was free to have multiple husbands, we would have a polygamous marriage with endless possibilities of relationships.

Wow… Good luck storing this simple example in a database.

Jim is married to Sarah, Sally, Cassy, and La’Tonya, while Sara is married to Jacob and Sally is married to Francis, who by the way is the husband of Tim’s wife, Michelle, Did I mention that Michelle was also married to gurrrrlll, the wife of Drake (who’s married to Sydney)?

Many to Many relationships over two tables cannot be stored in a database correctly. The only relationships that a RDBMS supports are one to one and one to many. This means that a many to many relationship must be broken up into two one to many relationships. This requires 3 tables.

The Best way to figure out Which Relationship it is…

The best way to do this is to write this down and fill in the the rest!

“One husband can…

one wife can…”

This would give us a 1 to 1:

One husband can have one wife.

One wife can have one husband.

This would give us a 1 to many:

One husband can have multiple wives.

One wife can have only one husband.

or

One wife can have multiple husbands.

but one husband can have only one wife.

This would give us a many to many:

One husband can have many wives.

one wife can have many husbands.

Obviously this can work with different things rather than marriage. We could do the same thing with instructors and classes:

This would give us a 1 to 1:

One instructor can teach only one class.

One class can be taught by only one instructor.

This would give us a 1 to many:

One instructor can teach multiple classes.

One class can be taught by only only one instructor.

or

One class can be taught by multiple instructors.

but one instructor can teach only one class.

This would give us a many to many:

One instructor can teach multiple classes.

Multiple classes can be taught by multiple instructors.

The one that we use depends upon the database we want to develop! Some schools will allow multiple instructors teaching a single class while others will only allow one! Or maybe we have adjunct instructors that are only allowed to teach one class! It just depends on the goal and purpose of the database.

The Relationship is Sometimes Decided by the Business Rules

When building a database for a business, company, ministry, school, or whatever, you will often follow business rules, which tell us what our database must accomplish. Image we got this letter…

“Dear database designer guy… I need a database for my college. Each instructor can only teach 10 classes and each class can be taught by up to 3 instructors. Thanks br0.”

We can define some notes and rules:

  • Database for a college
  • Instructor can teach up to 10 classes
  • A Class be taught by up to 3 instructors

The only important relationship we can get from this is an instructor-class relationship. Ignoring the number limits, we have:

  • Instructor – teaches – multiple classes
  • class – taught by – multiple instructors.

This is a many to many relationship!

  • One instructor can teach multiple classes
  • one class can be taught by multiple instructors
  • In conclusion: multiple instructors can teach multiple classes that can be taught by multiple instructors…… a never ending loop!

We can then later define the 10 classes and 3 instructors limit later in our final program.

From this example, we created a many to many relationship. NOTE: This does not mean all instructor-class relationships are many to many! We could have easily gotten this letter, instead:

“Dear database designer guy… I need a database for my college. Each instructor can only teach 10 classes and each class can only be taught by 1 instructor. Thanks br0.”

From this letter we create a 1 to many relationship.

Unfortunately, people will rarely specify the important details so you may need to clarify before designing a database (don’t guess!).

Why are Relationships Important?

Relationships are important because we design tables differently depending upon the relationships between entities.

In the previous blog I talked about atomic values and I gave this example of an incorrect table:

Sale IDBuyer IDBought.
3242032A new bicycle, 12 doughnuts, a shirt, a car,a girlfriend, pizza, a pet llama, a satchel

The biggest problem with this table is the “bought” column. The way this table is set up now is that “bought” is an attribute of the sale. But we can see that we are trying to say that one sale can have multiple items… this is automatically either a one to many or many to many relationship (it depends on the application). This tells us that, in fact, “bought” should actually be an entity (it’s own table), not an attribute! This is because we know that entities store multiple instances (or multiple items for sale in this specific case). We could have an items table where we can store multiple items rather than storing it all in the sales table! We will be getting into the specifics of designing relationships across tables in future blogs.

For now, know that usually:

A one to one relationship requires one table.

A one to many relationship requires two tables.

A many to many relationship requires three tables.

When studying relationships in more depth, we will learn about optional and required relationships as well as parent and child tables. This will be explained in the future when it is necessary.

Conclusion

  • The three relationships are also written as 1:1, 1:many, and many:many.
  • The type of relationship helps us determine how to design our database.
  • You will sometimes hear the relationships between tables as cardinality.

Further Study

Leave a Reply

Your email address will not be published. Required fields are marked *