Cardinality in data modeling is the property of a relationship that states how many instances of one entity can be associated with instances of another entity, expressed as one-to-one, one-to-many or many-to-many, often with explicit minimum and maximum counts. It is a core element of every entity-relationship model and determines how a relationship is implemented with keys and tables.
Within databases the word has a second meaning, the number of distinct values in a column, which matters for indexing and query optimization. That usage is not covered here; this page deals only with relationship cardinality as used in conceptual, logical and physical data models.
How cardinality describes relationships
Every relationship has two directions, and cardinality is stated for each side separately. In a relationship between customer and order, one customer may place many orders, while each order belongs to exactly one customer. Reading both directions gives the overall classification, here one-to-many.
Precise models distinguish maximum cardinality, the upper limit of related instances, from minimum cardinality, the lower limit. The minimum is also called optionality or modality: a minimum of zero means participation is optional, a minimum of one means it is mandatory. Saying only one-to-many therefore hides whether an order can exist without a customer.
| Type | Meaning | Typical example |
|---|---|---|
| One-to-one (1:1) | Each instance relates to at most one on the other side | Employee and employee badge |
| One-to-many (1:N) | One instance relates to many, each of those to one | Department and employees |
| Many-to-many (M:N) | Instances on both sides relate to many | Students and courses |
| Zero-or-one (0..1) | Optional single participation | Person and passport |
Cardinality should not be confused with the degree of a relationship, which counts how many entities take part in it. Most relationships are binary, but unary relationships, such as an employee who manages other employees, and ternary relationships, such as a supplier delivering a part to a project, also carry cardinality on every side. In ternary cases, the cardinality of each side is read with the other two entities held fixed, which makes careful wording essential.
Notations for cardinality in diagrams
Several diagram notations express the same information with different symbols. Choosing one notation and applying it consistently across a model is more important than the choice itself, because mixed notations are a frequent source of misreading.
| Notation | How cardinality is shown | Typical use |
|---|---|---|
| Crow’s foot | Line ends with bar, circle and fork symbols | Logical and physical models, database tools |
| Chen notation | Labels such as 1, N and M on relationship lines | Conceptual models, teaching |
| UML multiplicity | Ranges such as 0..1, 1, 1..*, 0..* | Class diagrams, object models |
| Min-max notation | Pairs such as (0,1) or (1,N) at each entity | Precise conceptual modeling |
Notations also differ in where the symbol is placed. In crow’s foot and UML, the symbol next to an entity describes how many of that entity participate; in min-max notation, the pair next to an entity describes how often that entity participates. Reading a diagram with the wrong convention inverts the relationship.
Implementing cardinality in a relational schema
When a logical data model becomes a physical schema, each cardinality maps to a specific arrangement of keys and constraints. Getting this mapping right is how the database itself enforces the business rules that the diagram describes.
| Cardinality | Physical implementation | Constraint that enforces it |
|---|---|---|
| One-to-many, mandatory | Foreign key in the many-side table | Foreign key declared NOT NULL |
| One-to-many, optional | Foreign key in the many-side table | Nullable foreign key |
| One-to-one | Foreign key in either table | Foreign key with a unique constraint |
| Many-to-many | Junction table with two foreign keys | Composite primary or unique key on both columns |
| Fixed maximum, for example 1 to 3 | Foreign key plus extra rule | Trigger, check logic or application validation |
Many-to-many relationships cannot be represented directly in relational tables, so they are always resolved through an associative or junction entity. That entity often acquires attributes of its own, such as an enrollment date between student and course, and may later become a full entity in the model.
Cardinality in dimensional and analytical models
In a star schema, the relationship between a dimension table and a fact table is normally one-to-many: one product row relates to many sales facts. Business intelligence tools rely on this declared cardinality to aggregate correctly and to decide in which direction filters propagate.
Problems arise when the real data violates the declared cardinality. A dimension that unexpectedly contains duplicate keys turns a one-to-many join into a many-to-many join, which multiplies fact rows and inflates totals. This fan-out error is one of the most common causes of wrong numbers in reports, and it is invisible unless uniqueness is tested.
Common mistakes in modeling cardinality
Cardinality errors are expensive because they are embedded in the schema and every application built on it. Most of them come from modeling the current state of the business instead of its rules over time.
- Ignoring history: a person has one current address, but over time has many, so a one-to-one model may be wrong.
- Omitting optionality, which leaves developers to guess whether a foreign key may be null.
- Forcing many-to-many into a delimited text column, which breaks integrity and querying.
- Choosing one-to-one by habit when the two entities could be merged into a single table.
- Never validating loaded data against the declared cardinality in analytical pipelines.
The most reliable remedy is to phrase each relationship as a sentence in both directions, such as each order is placed by exactly one customer, and to confirm it with domain experts before the schema is built.
Put simply
Cardinality answers a simple question about two kinds of things: how many of one can be linked to how many of the other. A mother can have several children, but each child has one birth mother. A student can take several courses, and each course has many students.
When people design a database, they write these rules down first. Getting the counts right means the database can protect the data, for example by refusing an order that belongs to no customer at all.
In practice
A hospital is designing a scheduling system. The first draft links each appointment to exactly one doctor, a one-to-many relationship between doctors and appointments. During review, nurses point out that some procedures require two or three clinicians at once.
The modelers change the relationship to many-to-many and introduce a junction entity called appointment participant, with a role attribute such as surgeon or anesthetist and a minimum of one participant per appointment. The corrected cardinality prevents a costly redesign after launch, and reports on clinician workload count each participant correctly instead of crediting only the first doctor.