-- Trying to model many-to-many without junction table

CREATE TABLE students (

student_id INT PRIMARY KEY,

course_ids VARCHAR(255) -- Storing comma-separated IDs

);

-- Proper junction table approach

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(100)

);

CREATE TABLE enrollments (

student_id INT,

course_id INT,

enrollment_date DATE,

PRIMARY KEY (student_id, course_id)

);

Comma-separated values cannot be queried efficiently, violate referential integrity, and make updates complex. Always use junction tables.

Storing multiple IDs in a single column violates First Normal Form

-- Missing indexes on junction table foreign keys

SELECT s.name, c.name

FROM students s

JOIN enrollments e ON s.id = e.student_id

JOIN courses c ON e.course_id = c.id;

-- Proper indexes on junction table

CREATE INDEX idx_enrollments_student ON enrollments(student_id);

CREATE INDEX idx_enrollments_course ON enrollments(course_id);

-- Or composite index

CREATE INDEX idx_enrollments_composite ON enrollments(student_id, course_id);

Junction tables are heavily queried. Index both foreign keys separately or use composite index for optimal performance.

Junction table queries are slow without proper indexes

-- Allowing duplicate relationships

INSERT INTO enrollments (student_id, course_id) VALUES (1, 101);

INSERT INTO enrollments (student_id, course_id) VALUES (1, 101); -- Duplicate!

-- Prevent duplicates with composite primary key

CREATE TABLE enrollments (

student_id INT,

course_id INT,

enrollment_date DATE,

PRIMARY KEY (student_id, course_id) -- Prevents duplicates

);