Entity Relationship Modeling

Entity Relationship Modeling

Entity relationship modeling is the first step in schema design. You sketch the domain as a graph of entities and the relationships that connect them, before writing any tables.

The three core pieces are:

• Entity a thing the business cares about, such as a customer or an order. Each becomes a table.
• Attribute a property of an entity, such as a name or a price. Each becomes a column.
• Relationship how two entities associate, such as a customer placing an order.

A relationship also carries cardinality, how many of one entity relate to the other. The common kinds are one to one, one to many, and many to many. Marking cardinality early reveals where you will need foreign keys and where you will need a junction table.

Good ER work separates identifying attributes that uniquely name an entity from descriptive attributes that merely describe it. This guides which columns become keys.

The payoff is a shared picture the whole team agrees on. Translating a clean ER diagram into tables is then almost mechanical, and ambiguity is caught while it is still cheap to fix on paper rather than in production data.

Key idea

Model the domain as entities, attributes, and relationships with explicit cardinality before translating that picture into tables.