Database Design

Good database design determines whether your system is maintainable, performant, and correct for years to come. The decisions made here are expensive to reverse in production.

The Design Process

1. Requirements analysis — understand the business domain. What entities exist? What attributes do they have? What are the relationships?
2. Conceptual model (ER Diagram) — draw entities, attributes, and relationships without worrying about SQL syntax.
3. Logical model — translate to tables, columns, and FK relationships.
4. Physical model — choose data types, indexes, constraints for the specific database engine.

Entity-Relationship Concepts

• Entity — a real-world thing that has data: Learner, Course, Facilitator, Assessment.
• Attribute — a property of an entity: Learner has name, age, city.
• Relationship — how entities connect: Learner ENROLS IN Course.
• Cardinality — how many of each side: one-to-one, one-to-many, many-to-many.

Cardinality Patterns

-- ONE-TO-MANY (most common):
-- One facilitator teaches many courses
-- Implemented: FK in the 'many' side table
CREATE TABLE courses (
    course_id      INT AUTO_INCREMENT PRIMARY KEY,
    title          VARCHAR(100) NOT NULL,
    facilitator_id INT,          -- FK to facilitators
    FOREIGN KEY (facilitator_id) REFERENCES facilitators(facilitator_id)
);

-- MANY-TO-MANY:
-- A learner enrols in many courses; a course has many learners
-- Implemented: a junction (bridge) table
CREATE TABLE enrolments (
    learner_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrol_date DATE NOT NULL,
    mark       DECIMAL(5, 2),
    PRIMARY KEY (learner_id, course_id),  -- composite PK
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

-- ONE-TO-ONE (rare):
-- Each learner has at most one detailed profile
CREATE TABLE learner_profiles (
    learner_id    INT PRIMARY KEY,  -- same as the learner's PK
    bio           TEXT,
    linkedin_url  VARCHAR(200),
    FOREIGN KEY (learner_id) REFERENCES learners(learner_id)
    -- No auto-increment — same ID as the learner
);

Naming Conventions

• Table names: plural, snake_case — learners, course_assessments.
• Column names: snake_case — full_name, monthly_salary.
• Primary keys: table_name_id or just id — learner_id, course_id.
• Foreign keys: same name as the referenced PK — learner_id in placements references learner_id in learners.
• Constraint names: type_table_column — pk_learners, fk_enrol_learner, uq_learners_email, chk_mark.

-- Full Your IT Tutor schema example:
-- learners (learner_id PK)
--   ↓ 1:many
-- enrolments (learner_id FK, course_id FK) ← many:many junction
--   ↓ many:1                                  ↑
-- courses (course_id PK) ←────────────────────┘
--   ↓ 1:many                  1:many
-- facilitators (facilitator_id PK) ←─ courses.facilitator_id FK
--
-- learners
--   ↓ 1:many
-- assessments (learner_id FK, facilitator_id FK)
--   ↓ 1:many
-- assessment_files (assessment_id FK)

-- Key design decisions:
-- 1. Always surrogate PK (auto-increment int)
-- 2. Many-to-many always via junction table
-- 3. Denormalise only when proven performance need

Practice Task

Common Mistakes

• Many-to-many without a junction table — storing a comma-separated list in a column is the most common database design mistake.
• Inconsistent naming — mixing camelCase and snake_case, singular and plural table names.
• Storing derived data redundantly — if you can calculate a value from other data, don't store it separately.
• Designing for today only — leave room for growth: VARCHAR(200) not VARCHAR(20) for names.