ER diagram to table mapping in SQL

Introduction

We use ER diagrams to plan how data is organized. Mapping them to tables helps us create the actual database structure.

When designing a new database for a small business.

When converting a paper design of data relationships into a working database.

When explaining database structure to team members using visuals and then building it.

When updating an existing database to match new data requirements.

When learning how to organize data logically before coding.

Syntax

SQL

-- Basic steps to map ER diagram elements to tables
-- 1. Each entity becomes a table
-- 2. Each attribute becomes a column
-- 3. Primary keys become primary keys in tables
-- 4. Relationships become foreign keys or new tables

CREATE TABLE EntityName (
  PrimaryKeyColumn datatype PRIMARY KEY,
  Attribute1 datatype,
  Attribute2 datatype,
  ...
);

-- For many-to-many relationships, create a new table with foreign keys
CREATE TABLE RelationshipName (
  Entity1_ID datatype,
  Entity2_ID datatype,
  PRIMARY KEY (Entity1_ID, Entity2_ID),
  FOREIGN KEY (Entity1_ID) REFERENCES Entity1(PrimaryKey),
  FOREIGN KEY (Entity2_ID) REFERENCES Entity2(PrimaryKey)
);

Each entity in the ER diagram becomes a table in the database.

Relationships can be represented by foreign keys or separate tables depending on their type.

Examples

This creates a table for the 'Student' entity with ID, name, and age.

SQL

CREATE TABLE Student (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100),
  Age INT
);

This creates a table for the 'Course' entity.

SQL

CREATE TABLE Course (
  CourseID INT PRIMARY KEY,
  Title VARCHAR(100),
  Credits INT
);

This table represents a many-to-many relationship between students and courses.

SQL

CREATE TABLE Enrollment (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID),
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Sample Program

This example maps two entities: Author and Book. Each author has many books, so Book has a foreign key AuthorID.

SQL

CREATE TABLE Author (
  AuthorID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Book (
  BookID INT PRIMARY KEY,
  Title VARCHAR(100),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

Important Notes

Always define primary keys to uniquely identify each row.

Use foreign keys to link related tables and keep data consistent.

Many-to-many relationships need a separate table with foreign keys from both entities.

Summary

ER diagrams help plan database tables and their relationships.

Entities become tables, attributes become columns.

Relationships become foreign keys or new tables depending on type.