ER modeling basics

Entity-Relationship (ER) diagrams visualize tables as entities, columns as attributes, and lines as relationships (one-to-many, many-to-many). Design on paper before writing CREATE TABLE.

Entities and relationships

• One-to-many — one customer, many orders (FK on orders)
• Many-to-many — students and courses via enrollment junction table
• One-to-one — user and profile split for optional heavy columns

Junction table

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_at TEXT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

Practice: Sketch ER on paper, then implement in SQLite.

Cardinality notation

Crow's foot diagrams mark “many” sides. Validate designs by walking sample user stories: enroll student, list course roster, drop course.

Important interview questions and answers

1. Q: Many-to-many implementation?
   A: Two foreign keys in a junction table with composite primary key.
2. Q: When split one-to-one?
   A: Optional profile data, security isolation, or performance partitioning.

Self-check

1. How do you model students taking many courses?
2. Where does the foreign key go in one-to-many customer/orders?

Tip: Many-to-many always needs a junction table with two foreign keys.