Entity-Relationship Diagrams: Modelling Data Structures

Alex: Today we're covering Entity-Relationship diagrams, which are the standard tool for planning a database design. Sam, what does an ER diagram actually show us?

Sam: An ER diagram is a visual representation of the data model. It shows the entities, the attributes of those entities, and the relationships between them. It's the blueprint from which a database schema is derived, and it's also a communication tool: it lets you discuss and validate your data model with stakeholders before you start writing any SQL.

Alex: What's the notation like?

Sam: There are a few different notation styles, but the most common in academic and professional settings are Chen notation and Crow's Foot notation. In Chen notation, entities are represented as rectangles, relationships as diamonds, and attributes as ellipses. In Crow's Foot notation, which is cleaner and more widely used in tools like MySQL Workbench and Lucidchart, entities are rectangles with their attributes listed inside, and relationships are represented as lines with symbols at each end that indicate the cardinality.

Alex: Let's talk about cardinality. What does that mean?

Sam: Cardinality describes how many instances of one entity can be associated with instances of another. The three types are one-to-one, one-to-many, and many-to-many. A one-to-one relationship might exist between a person and their passport: one person has at most one current passport, and each passport belongs to exactly one person. A one-to-many relationship is extremely common: one customer can place many orders, but each order belongs to exactly one customer.

Alex: And many-to-many?

Sam: Many-to-many relationships are where it gets more interesting. A student can be enrolled in many courses, and each course can have many students enrolled. In a relational database, you can't directly represent a many-to-many relationship between two tables. You have to resolve it by introducing a third table, called a junction table or associative entity, that sits between the two. That table would have a foreign key to the student table and a foreign key to the course table, with each row representing one enrolment.

Alex: How do you go about constructing an ER diagram in practice?

Sam: Start by identifying the main entities: the things about which you need to store information. Then list the attributes of each entity. Then think about the relationships: for each pair of entity types, ask whether instances of one can be associated with instances of the other, and if so, what the cardinality is. Draw the diagram, review it with stakeholders to check it accurately represents the domain, and refine it until it's correct before moving on to schema design.

Alex: Are there common mistakes to avoid?

Sam: A few. Don't model relationships as attributes; give each relationship its own representation. Be careful about whether attributes belong to an entity or to a relationship; in the student-course example, the grade might be an attribute of the enrolment relationship rather than of the student or the course. And make sure your primary keys are appropriate; using composite keys from the natural world, like a combination of first name and last name, is usually a bad idea because those can change.

Alex: Great. So ER diagrams are both a thinking tool and a communication tool.

Sam: Exactly. The act of drawing the diagram forces you to think through the data model carefully, and the resulting diagram gives you something concrete to discuss and validate. It's an essential step in any database project.

Alex: Thanks Sam. Next we're looking at normalisation.