Data Modeling & Schema Design
Entity-Relationship Modeling
Entity-relationship modeling turns requirements into identifiable entity sets, descriptive attributes, relationships, and participation constraints. Its value is not the drawing itself but the explicit decisions about identity, cardinality, and optionality.
- An entity has independent identity; an attribute describes an entity or relationship.
- Cardinality states the maximum number of participants; optionality states the minimum.
- Many-to-many relationships become associative relations.
- Weak or dependent entities need the owner identity plus a discriminator.
- Relationship roles must be named when the same entity participates more than once, such as
billing_customerandrecipient_customer. - Generalization and specialization require explicit completeness and overlap rules before choosing a table mapping.
Cardinality and optionality in the commerce core
| Relationship | Rule from left to right | Rule from right to left |
|---|---|---|
| Customer–Order | A customer may place zero or many orders | Each order has exactly one customer |
| Order–Order item | Each order has one or more lines | Each line belongs to exactly one order |
| Product–Order item | A product may appear on zero or many lines | Each line names exactly one product |
| Product–Category | A product may have zero or many categories | A category may classify zero or many products |
Mapping the model creates foreign keys on the many side and bridge relations for many-to-many relationships. Some minimum-cardinality rules are not row local: inserting the order header before its first line temporarily violates “one or more,” so enforce that rule at a transaction or workflow boundary rather than inventing a nullable line column on orders.