Databases — Understanding Relationship Types
One of the most important things to learn and understand when dealing with databases is relationship types and how each of them work. These relationship types help you build your data structure and also ensure that your data is accurate when analyzing it using tools like Looker or Tableau.
Below are the different relationship types:
- One-to-One
- One-to-Many / Many-to-One
- Many-to-Many
- Self Referencing
With that being said, the main one that you will be dealing with is the second option: One-to-Many / Many-to-One. Although all of these are very important in their own right, it would be best to fully understand the One-to-Many / Many-to-One since this is used the majority of the time.
For me personally, once I had a good grasp on the One-to-Many / Many-to-One relationship type it helped me to fully understand the remaining relationship types and how data models work with one another. To hopefully help others, I decided to create this blog about the One-to-Many / Many-to-One relationship.
One-to-Many
When I think about this relationship I like to think of it in terms of an Excel spreadsheet and doing a vlook-up to combine data. What is the unique value that I can use to combine the two spreadsheets into one? Thinking about it this way allows me to break it down into simpler terms.
To better explain this I created two simple tables to illustrate how this relationship type works:
Customer Table
Customer Inquiries
In order to join these two tables we will need to understand their relationship. The Tickets table includes the customer_id but this table only allows for one customer_id per inquiry. We can then use the id in the customer table to connect the data from the ticket table using the customer_id. This indicates to us that this will be a One-to-Many relationship since each ticket will only have ONE customer_id but customers can be connected to MANY tickets.
The diagram below is another way to think about how the two models above connect with one another:
Hopefully this helped you have a better understanding about database relationships and will accelerate your journey into databases!
