Skip to content

Relationships

Before we go further with the SQL language, we need to learn how to actually design relational databases. In this part of the course couple new concepts called relationships and normalization will be introduced and we will actually design a few different databases.

Relationships

Databases in real-life can grow large and complex and can get really messy. In order to more easily represent complex data, we have the definition of relationships that can be used to ease the representation of data.

There are three kinds of relationships available in relational databases:

  • One to one relationship
  • One to many relationship
  • Many to many relationship.

Before moving on to the details of the relationships, below you can find a very simple and crude example of a diagram that shows how a game shop could create database that holds their customers, games, orders, reviews for games and such information. The customerDetails table contains more information about the customer, like their address and phone number:

One to One Relationship

This is the most infrequently used relationship type. In this kind of a relationship, we determine that one single entity in a table belongs to one single entity in another table.

In our example, only the customers table has one to one relationship with the customerAddresses table. This is because we can only have one unique customer in the customers table and each unique customer can only have their details once.

One to Many Relationship

This is the most commonly used relationship model. In this model, item in one table can have a relationship with multiple items in another table.

Couple examples of this includes:

  • A single game can contain multiple reviews written of it (games -> gameReviews)
  • Customer can have multiple orders (customers -> orders)

Many to Many Relationship

This is relatively common, but not as common as the one to many relationship. In this kind of a relationship many entities relate to each another.

Couple examples of this includes:

  • Multiple game companies can have multiple games
  • Multiple game genres can be in multiple games

A Note About the Relationships

Most of the relationships are point of view thing. So, while a game could have one genre (one to many), multiple genres can be in multiple games (many to many). So depending on what entity we inspect, this point of view can change.

Conclusion

There are 3 types of relationships in relational databases:

  • one to one (infrequently used)
  • one to many (most frequently used)
  • many to many (frequently used)
  • Relationships are a point of view thing.