Entity relationship diagram (ERD)

An entity relationship diagram (ERD) is a type of diagram that depicts the entities of interest in a domain and the relationships between them. It is the most common visual representation of an data model, and a standard artifact for designing and documenting relational databases. The notation was introduced by Peter Chen in 1976 as the entity-relationship (ER) model, which proposed a graphical, data-centric alternative to the relational and network models of the time.

An ERD is a picture of a data model, not the model itself. The underlying model is a structured, non-visual artifact; the diagram is one view onto it. The distinction matters because two modelers given the same notation can still produce different ERDs from the same requirements, so the diagram is a means of communication rather than a guarantee of a correct design.

Components

An ERD is built from a small set of elements.

  • Entity. A thing or concept the data model must record, eg. Customer, Order, or Product. In Chen’s notation an entity is drawn as a rectangle. In a relational database an entity maps to a table.
  • Attribute. A property of an entity, eg. name, email, or price. Chen draws attributes as ovals connected to their entity. In a relational schema an attribute maps to a column.
  • Relationship. An association between entities, eg. a Customer places an Order. Chen draws relationships as diamonds connected to the participating entities. In a relational schema a relationship is implemented through foreign keys or a join table.
  • Cardinality and optionality. How many instances of one entity may participate in a relationship with another, and whether participation is mandatory. The common forms are one-to-one, one-to-many, and many-to-many. Notations differ mainly in how they encode these two properties.

Notations

Several notations are in use, most of them descendants of Chen’s original.

  • Chen’s notation. The original 1976 form, with rectangles for entities, ovals for attributes, and diamonds for relationships. Cardinality is marked with 1, N, or M on the lines connecting entities to relationships.
  • Crow’s foot notation. The most widely used form in modern database design tools. Entities are rectangles listing their attributes, and a crow’s foot at one end of a connecting line denotes "many" while a single bar denotes "one". Optionality is shown with a circle versus a bar.
  • Barker’s notation. A compact form popularized by Richard Barker at Oracle, using crow’s feet for cardinality and a dashed line for optional relationships. It drops the relationship diamond of Chen’s notation.
  • IDEF1X. A US federal standard notation developed in the 1980s, more rigid than crow’s foot and often used in government and defense work.

The notations differ mainly in how they represent cardinality and optionality, but the underlying model, entities plus relationships plus attributes, is shared.

Levels

ERDs are drawn at three levels that mirror the conceptual, logical, and physical schemas of data modeling.

  • Conceptual ERD. High-level and technology-independent. It names the entities and their relationships but omits attributes and keys, and is used to align with business stakeholders before any database product has been chosen.
  • Logical ERD. Adds attributes, primary keys, and foreign keys, and applies normalization to remove redundancy. It is still independent of a specific database product.
  • Physical ERD. Fixes data types, indexes, and product-specific constraints, and is expressed in a data definition language such as SQL.

ERDs and class diagrams

ERDs are sometimes compared with the class diagrams of UML. Both depict things and their relationships as a graph of boxes and lines, but they answer different questions. An ERD describes the data a system must store, organized for relational storage and referential integrity. A UML class diagram describes the structure of an object-oriented program, including behavior (methods), inheritance, and packaging. An entity in an ERD is not the same as a class in a program, even when they share a name. Many projects use both, the ERD to design the database and the class diagram to design the application.

See also

References

  • Chen, P. P. (1976). "The entity-relationship model: toward a unified view of data". ACM Transactions on Database Systems, 1(1), 9–36.