Now that you know the cardinality meaning and have an idea of what a one-to-one relationship is, the one-to-many concept will be easy to grasp.
In this case, you have a column with unique entries (column “A”). Each entry can be related to multiple entries in another column (column “B”). As such, anything in column A can link to multiple things in B, but each item in B will only link to one thing in A.
An easy example to understand this would be matching sales representatives to their clients. Each rep can have dozens of clients, but to keep relationships stable, each client only has one sales representative. It’s a one-to-many relationship.
Lastly, we can cover the many-to-many relationship. Any item in column A can have multiple pairs in column B; the reverse is also true.
We can actually use the sales representative example to think about many-to-many relationships.
What if the company is a microchip manufacturer with massive, multi-billion-dollar clients? That company most likely has well-defined sales teams, and each team might handle a few clients. In this case, any client has multiple individual reps, and each agent might work on a couple of different contracts.
This is the idea of a many-to-many relationship.
In the previous sections, you learned about different types of cardinal arithmetic and relationships with some simplified examples. Now, we can look at some real-world applications of cardinality to see exactly how it can function in a database.
Let’s think about designing our own database that would function for Uber.
It’s a well-known company with a well-known model, but if you're not familiar, here’s how it works. Uber is a smartphone app. You can open the app and request a ride from your current location to anywhere else. The app will connect you with a driver, assign the fair, figure out the navigation, and handle all logistics. Your driver will show up, take you where you want to go, and you'll pay for the ride via the app.
For such a large and complicated app, databases built from intent data are definitely in play, and one that's easy to imagine is a database that keeps track of customers and drivers. In this case, every driver and customer needs a unique identifier.
That means that our driver column (D) and our customer column (C) both have high cardinality (in fact, they have infinite cardinality). Every item in each column is unique.
We can also think about the relationships between the columns. At a glance, it might seem like any driver can have multiple passengers and vice versa, but we actually have one-to-one relationships for a transaction. A driver can only have one customer at a time—even if the customer is technically a group of people, there will only be one customer ID on the transaction. We need a one-to-one database to match customers and drivers for any individual transaction.
If any company in the world uses databases, it’s Amazon. For this example, let’s consider the Amazon online store. There are a lot of retailers who have shops on the website, and there are billions of people who shop via Amazon.
Once again, every shop and every shopper needs a unique ID in order to manage each transaction. However, this case is more complicated than the Uber example.
When you shop on Amazon, you can purchase things from multiple retailers, all in the same transaction. Amazon handles the logistics, so you don’t need to know if your items originate from different locations. For you, it’s a single transaction.
Meanwhile, shops can also sell to multiple users simultaneously, but each transaction will only be with one user.
In this case, we have a one-to-many relationship. You can buy from multiple stores, but each store is only selling to you in that particular transaction.
Enhance data modeling with cardinality
Now that you know more about data cardinality and how it works with databases and data modeling, the next step is to dive in and explore tools that can deepen your understanding and empower you to work directly with databases.
Mailchimp reports make it easy to assess data and gauge cardinality with an easy-to-read dashboard. Try Mailchimp today.