Theory
One order, or a hundred?
Meera's design has a Customer and an Order, linked by 'places'. But a crucial question decides how the tables actually work: how many? Does one customer place one order, or many?
That 'how many' is called cardinality, and getting it right is the difference between a database that works and one that loses data. One kind of cardinality, many-to-many, is so demanding it forces you to add an extra table, exactly the insight that will explain Meera's whole design. This lesson is the 'how many' of relationships.
Theory
Counting connections in a family
Think family relationships. A person and their Aadhaar number: exactly one each way (one-to-one). A mother and her children: one mother, many children (one-to-many). Students and the subjects they take: each student takes many subjects, each subject has many students (many-to-many). You already understand cardinality intuitively, it is just counting how many of each side connect.
At a glance
The cardinalities
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | Each side, at most one | Person to Aadhaar |
| One-to-many (1:N) | One relates to many | Customer to Orders |
| Many-to-one (N:1) | The same, seen from the many side | Orders to Customer |
| Many-to-many (M:N) | Each side relates to many | Students to Courses |
Theory
The many-to-many problem
One-to-one and one-to-many fit neatly into tables. But many-to-many cannot be stored directly in two tables, where would you put the links? One customer buys many items and one item is bought by many customers; neither table has room for a list.
The fix is a junction (linking) table in between. For a shop, that is the Orders table: it breaks the messy M:N (customers to items) into two clean one-to-many relationships (one customer to many orders, one item appearing in many orders). Every many-to-many becomes a middle table.
Quiz
In a college, each student enrols in many courses, and each course has many students. What cardinality is this, and what does implementing it require?
- Many-to-many, requiring a junction/linking table
- One-to-many, storable in two tables directly
- One-to-one, needing no extra table
- Many-to-one, just a foreign key
Show the answer
Many-to-many, requiring a junction/linking table
Each side connects to many of the other, so it is many-to-many (M:N). This cannot live in just the Student and Course tables; it needs a junction table (an Enrollment table) holding pairs of student-and-course, splitting the M:N into two one-to-many relationships. 'Students and courses' is THE textbook M:N example, and the junction-table requirement is the key exam point.
Think first
Spot the cardinality
Meera says: 'each of my orders belongs to exactly one customer, but a customer can have many orders.' Reading it from the CUSTOMER side, what cardinality is this? And does it need a junction table?
Show the answer
From the customer's side it is one-to-many (1:N): one customer, many orders. (From the order's side the same relationship is many-to-one, same link, opposite viewpoint.) It does not need a junction table, one-to-many stores directly by putting the customer's key into each order row. Only many-to-many demands the extra middle table. 1:N is the most common relationship in real databases.
Watch out
Where marks leak
Missing that many-to-many needs a junction table, the single most important implementation fact here. Confusing one-to-many and many-to-one: they are the same relationship viewed from opposite ends, not different designs. And giving weak examples, use the crisp classics: person-Aadhaar (1:1), mother-children or customer-orders (1:N), students-courses (M:N). Exams ask you to both name and give an example of each.
Theory
This explains Meera's whole design
Now it makes sense why a shop database is never just 'customers and items': it needs an Orders table in the middle, precisely because customers-to-items is many-to-many. That middle table is where keys do their work, which is the next set of lessons. Cardinality is the reason database designs have the tables they do. Next: what an entity needs to survive on its own, strong vs weak entities.
Summary
Key takeaways
- Cardinality = how many of one entity relate to another.
- One-to-one (1:1): each side at most one (person to Aadhaar).
- One-to-many (1:N): one relates to many (customer to orders); most common; stores directly.
- Many-to-one is the same 1:N relationship seen from the other side.
- Many-to-many (M:N): each side relates to many (students to courses); needs a junction/linking table.
- Memory hook: person-Aadhaar (1:1), mother-children (1:N), students-courses (M:N).