Introduction
Database design is the process of turning a business problem into a set of tables, columns, and relationships that store data accurately and efficiently. An Entity-Relationship (ER) diagram is the standard tool used to plan this structure before any table is created. It captures entities (things you track, like Customer or Order), their attributes (name, email, price), and the relationships between them (a Customer places many Orders). Getting this modeling step right up front prevents costly redesigns later, since every application feature ultimately depends on the shape of the underlying schema.
Cricket analogy: Before a tour begins, team management plans the squad, roles, and travel schedule on paper like an ER diagram, mapping 'Player' to 'Match' so nobody discovers gaps once the series starts.
Syntax
-- Translating an ER diagram into SQL: Customer (1) --- (M) Order
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Explanation
In an ER diagram, entities become tables and attributes become columns. Relationships are expressed through cardinality: one-to-one, one-to-many, or many-to-many. A one-to-many relationship (one customer, many orders) is implemented by placing a foreign key on the 'many' side, here customer_id in orders. A many-to-many relationship, such as Students enrolling in many Courses, cannot be represented directly with two tables; it requires a junction (bridge) table holding foreign keys to both sides, plus any attributes specific to the relationship itself, like an enrollment_date.
Cricket analogy: One captain leads many matches (one-to-many, captain_id on the match), but a player appearing in many matches while a match has many players needs a junction 'scorecard' table linking player_id and match_id.
Example
-- Many-to-many: Students <--> Courses via a junction table
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(150) NOT NULL
);
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);Analysis
The enrollments table resolves the many-to-many relationship: each row links exactly one student to exactly one course, and the composite primary key (student_id, course_id) prevents duplicate enrollments while allowing a student to take many courses and a course to have many students. Good ER modeling also forces you to decide cardinality and optionality early, e.g. can an order exist without a customer? Answering these questions during design avoids ambiguous or inconsistent data once the application is live.
Cricket analogy: A composite key of (player_id, match_id) in the scorecard table stops the same player being logged twice for one match, and designers must also decide upfront whether a match can exist without an assigned umpire.
Key Takeaways
- Entities become tables, attributes become columns, relationships become keys.
- One-to-many relationships use a foreign key on the 'many' side.
- Many-to-many relationships require a junction table with a composite key.
- ER diagrams should be finalized before writing CREATE TABLE statements to avoid rework.
Practice what you learned
1. In ER modeling, what does an 'entity' typically become in the physical database?
2. How is a many-to-many relationship between Students and Courses implemented?
3. Where is the foreign key placed in a one-to-many relationship between Customer and Order?
4. What is the main purpose of creating an ER diagram before writing SQL?
Was this page helpful?
You May Also Like
Primary and Foreign Keys
Understand how primary keys uniquely identify rows and foreign keys enforce relationships between tables.
The Relational Model
Understand how the relational model organizes data into tables of rows and columns, based on set theory and predicate logic.
Normalization: 1NF to 3NF
Learn the step-by-step process of eliminating data redundancy and anomalies through the first three normal forms.