ER model: Designing databases

In short: An entity-relationship model sketches which things (entities) exist, which properties they have and how they relate, before tables are created.

In more detail: Before building a database you should understand what is stored. The ER model (after Peter Chen, 1976) is a plan for that. From it you later derive the tables. In vocational training it is a fixed exam topic.

In Depth

Building blocks

• Entity: an object of reality, for example customer, order, article (later becomes a table).
• Attribute: property, such as name, price. The key attribute identifies an entity uniquely (customer number).
• Relationship: connection between entities, for example “customer places order”.
• Cardinality: how many entities are involved.

Cardinalities

• 1 : 1: a person has exactly one ID card.
• 1 : n: a customer has many orders, each order belongs to one customer.
• n : m: students take several courses, a course has several students. The notation can use (min, max) specifications, crow’s foot or UML class diagrams.

From the ER model to tables

• Each entity becomes a table, each attribute a column, the key attribute the primary key.
• 1 : n: the primary key of the “1” side becomes a foreign key in the “n” table (order.customer_id).
• n : m: needs an intermediate table with both foreign keys (enrolment(student_id, course_id)), often with attributes of its own such as grade.
• 1 : 1: foreign key with UNIQUE or a shared table.

Good design

Do not store data twice but link it. If an address changes it should happen in one place. The normal forms (normalisation) serve this: first normal form (atomic values), second (no partial dependencies on the key), third (no dependencies between non-key attributes). For performance one can deliberately denormalise. Tools: draw.io, dbdiagram.io, MySQL Workbench, pgAdmin.

See also: Database, SQL, Modelling: UML, EPC, BPMN, SQL index