Database Design: Entities, Relationships and ER Diagrams
Turn real-world requirements into well-shaped tables before you write a single query
Big Idea: Good SQL starts long before
SELECT. A database design decides what tables exist, what each column holds, and how tables connect. Get the design right and queries become short and honest; get it wrong and you fight the data forever. In this lesson you translate plain-language requirements into entities (tables), attributes (columns), and relationships (foreign keys and junction tables), then draw and read an ER diagram.
By the end you will be able to:
- spot entities and their attributes in a requirement,
- choose a primary key and connect tables with a foreign key,
- model a one-to-many relationship (FK on the "many" side),
- model a many-to-many relationship with a junction table,
- and read an ER diagram with crow's-foot notation.
1. Entities and attributes
An entity is a type of thing your application cares about and needs to remember: a Student, a Course, a Teacher, an Order. In a relational database each entity becomes a table. An attribute is a fact about that entity - a name, an email, a year - and each attribute becomes a column. One real thing (one specific student) becomes a row.
We will design a small school database across the whole lesson. Start with the simplest entity, the teacher:
CREATE TABLE teachers (
teacher_id INT PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(120) UNIQUE
);Three design decisions are already visible: the entity (teachers), its attributes (name, email), and a primary key (teacher_id) that gives every row a unique, stable identity.