When you’re designing a relational database, you need to define how tables relate to each other. You do this by creating relationships between the tables. Relationship cardinality describes how many records in one table can be associated with records in another table. It’s a fundamental concept that affects how you structure your database and write your queries.
Understanding cardinality helps you avoid design mistakes that lead to data integrity issues, performance problems, or queries that return unexpected results.
The Basic Types
There are three main types of relationships based on cardinality:
- One-to-one (1:1) means each record in Table A relates to at most one record in Table B, and vice versa.
- One-to-many (1:N) means each record in Table A can relate to multiple records in Table B, but each record in Table B relates to only one record in Table A. You’ll sometimes see this referred to as many-to-one (N:1) when described from the other direction (many orders belong to one customer). It’s the same relationship, just a matter of perspective.
- Many-to-many (M:N) means records in Table A can relate to multiple records in Table B, and records in Table B can relate to multiple records in Table A.
These relationships determine how you structure your foreign keys and whether you need junction tables to connect your data.
One-to-One Relationships
In a one-to-one relationship, each record in one table corresponds to exactly one record in another table. This is the least common type of relationship.
A classic example is a users table and a user_profiles table. Each user has one profile, and each profile belongs to one user. You might split this data for organizational reasons (e.g., to keep authentication information separate from profile details) or for performance reasons if the profile contains large text fields or images that aren’t always needed.
Another example might be employees and company_cars in an organization where each employee gets assigned one car, and each car is assigned to one employee.
In a one-to-one relationship, you can place the foreign key in either table. If the relationship is optional on one side (like some employees have company cars, but not all), you’d typically put the foreign key on the optional side to avoid null values.
One-to-Many Relationships
One-to-many is the most common relationship type. One record in the parent table relates to many records in the child table, but each child record relates to only one parent.
Think about customers and orders. One customer can place many orders, but each order belongs to one customer. Or consider blog posts and comments. One post can have many comments, but each comment belongs to one post.
The foreign key always goes in the “many” side of the relationship. For example, the orders table has a customer_id foreign key pointing back to the customers table. Or using the comments example, the comments table might have a post_id foreign key.
This is how you model hierarchical relationships in relational databases. Departments have many employees, categories have many products, authors have many books. The pattern repeats everywhere.
Many-to-Many Relationships
Many-to-many relationships exist when records on both sides can relate to multiple records on the other side. Students enroll in many courses, and courses have many students. Products can have multiple tags, and tags apply to multiple products.
You can’t directly represent many-to-many relationships with just foreign keys. If you tried putting a student_id in the courses table, you could only store one student per course. If you put a course_id in the students table, you could only store one course per student.
The solution is a junction table (also called a bridge table, join table, or associative table) that sits between the two tables. For students and courses, you’d create an enrollments table with both student_id and course_id as foreign keys. Each row represents one student enrolled in one course.
The junction table effectively breaks the many-to-many relationship into two one-to-many relationships. One student has many enrollments, one course has many enrollments, and each enrollment links one student to one course.
Junction tables often include additional attributes about the relationship itself. An enrollments table might include enrollment_date, grade, or status. An order_items table (linking orders to products) might include quantity and price.
Cardinality Notation
When you’re drawing database diagrams or entity-relationship diagrams, cardinality is indicated with symbols at the ends of relationship lines.
Common notations include:
- Crow’s foot notation uses a line with three branches (like a crow’s foot) to indicate “many” and a single line or dash to indicate “one”. A circle or ring indicates “optional” (zero allowed).
- Chen notation uses “1”, “N”, or “M” labels on the relationship lines.
- Min-max notation shows the minimum and maximum number of relationships, like (0,N) or (1,1). You might also see asterisks (
*) used to represent “many”, especially in UML diagrams and text-based notations.
Here’s an example:
This uses 1 and a single line to indicate the “one” side of the relationship. For the “many” side, it uses crow’s foot notation and an asterisk (*).
The specific symbols vary, but they all communicate the same fundamental information, which is how many records on each side can participate in the relationship.
Optional vs Required Relationships
Cardinality also describes whether relationships are optional or required, sometimes called participation.
A required relationship means every record must have a related record. For example, every order must have a customer. You can’t have an order without knowing who placed it.
An optional relationship means some records might not have a related record. A customer might not have any orders yet. An employee might not have a company car assigned.
This affects whether foreign key columns allow NULL values. If every order must have a customer, customer_id in the orders table should be defined as NOT NULL. If not all employees have cars, car_id in the employees table should allow NULL.
Cardinality and Database Design
Getting cardinality right is absolutely crucial for database design. If you model a one-to-many relationship as many-to-many, you’re adding unnecessary complexity with a junction table you don’t need. If you model a many-to-many relationship as one-to-many, you’ll run into data integrity problems or won’t be able to represent your data correctly.
Consider the relationship between authors and books. In a simple case, you might think this is one-to-many. After all, one author writes many books. But what about co-authored books? If books can have multiple authors, it’s actually many-to-many, and you need a junction table.
Understanding the real-world relationship you’re modeling is essential. Talk to domain experts, understand the business rules, and think about edge cases. Can an order have multiple shipping addresses? Can a product belong to multiple categories? These questions determine your cardinality choices.
Cardinality in Queries
Cardinality affects how you write queries and what results you get. When you use an inner join on tables with a one-to-many relationship, the result set will have as many rows as exist on the “many” side (assuming no additional filtering). If a customer has 5 orders and you join customers to orders, you’ll get 5 rows for that customer (one per order).
I should point out that different join types will produce different results. A left join from the “one” side will include customers with no orders (creating NULL values for order columns), while a full outer join could include both customers without orders and orders without customers, depending on your data and constraints.
With many-to-many relationships through junction tables, you need to join through the middle table. To find all courses for a student, you join students to enrollments to courses. To find all students in a course, you join the other direction.
Understanding cardinality also helps you predict query performance. Joining tables where the relationship is one-to-one is straightforward. Joining with one-to-many can multiply your result rows. Many-to-many joins through junction tables add another layer of complexity and potential performance considerations.
Common Mistakes
A frequent newbie mistake is forcing a many-to-many relationship into a one-to-many structure by allowing comma-separated values or JSON arrays in a column. Storing multiple product IDs in an order column or multiple tag IDs in a post column breaks relational principles and makes querying difficult.
That said, there are cases where such a design is deliberately chosen. For example, when using document databases, dealing with simple tag lists that are rarely queried individually, or when the performance cost of joins outweighs the benefits of normalization. The main point to remember here is to make this choice intentionally with understanding of the tradeoffs. Not stumbling into it by mistake.
Another common mistake is creating one-to-one relationships when the data should just be in the same table. If every user always has exactly one profile and you always query them together, they probably belong in one table. However, splitting tables can make sense for performance (separating frequently accessed from rarely accessed columns), security (isolating sensitive data), or organizational reasons (different teams managing different aspects of the data). So again, this needs to be a deliberate choice rather than an accident.
Misidentifying cardinality during design can lead to problems later. You build a system assuming one-to-many, then discover you need many-to-many and have to refactor your entire schema. Understanding the true nature of your relationships upfront will save significant rework.
Cardinality in Practice
When designing a database schema, start by identifying the entities and then carefully consider how they relate. For each relationship, ask:
- Can an entity of Type A relate to multiple entities of Type B?
- Can an entity of Type B relate to multiple entities of Type A?
- Are these relationships required or optional?
The answers determine your cardinality and guide your implementation. One-to-many gets a foreign key. Many-to-many gets a junction table. Optional relationships allow NULLs.
Getting cardinality right from the start makes your database easier to work with, your queries simpler to write, and your data more consistent and reliable.