You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Good schema design prevents performance problems and maintenance headaches before they start. This lesson covers normalisation, denormalisation, key strategies, soft deletes, audit trails, and polymorphic associations.
Normalisation eliminates redundancy and ensures data integrity by organising data into related tables.
Each column contains a single, atomic value. No repeating groups.
-- Violates 1NF: multiple phone numbers in one column
CREATE TABLE contacts_bad (
id SERIAL PRIMARY KEY,
name TEXT,
phones TEXT -- '020-1234, 020-5678'
);
-- 1NF: separate table for phone numbers
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE phone_numbers (
id SERIAL PRIMARY KEY,
contact_id INT REFERENCES contacts(id),
phone TEXT NOT NULL,
label TEXT -- 'home', 'work', 'mobile'
);
All non-key columns depend on the entire primary key (relevant for composite keys).
-- Violates 2NF: student_name depends only on student_id, not on (student_id, course_id)
CREATE TABLE enrolments_bad (
student_id INT,
course_id INT,
student_name TEXT, -- depends only on student_id
grade CHAR(1),
PRIMARY KEY (student_id, course_id)
);
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.