Schema Design: Entities, Attributes and Relationships

Concept

Schema design turns sentences about a business into tables. The method is mechanical enough to be taught:

1. Find the entities - the nouns the business cares about. Each becomes a table.
2. Find the attributes - the facts about each entity. Each becomes a column.
3. Find the relationships - the verbs connecting entities. Each becomes a foreign key or a junction table.
4. Choose keys - one primary key per table, UNIQUE for the other candidate keys.
5. Add constraints - encode every rule the data must always obey.

Worked example

A training company runs courses. Each course is taught by one trainer and has many sessions on different dates. Students enrol on sessions, and each enrolment records a status and a fee paid.

Step 1: entities

The nouns in bold: courses, trainers, sessions, students, plus enrolments for the student-session pairing.

Step 2 and 3: attributes and relationships

Step 4 and 5: the schema

CREATE TABLE trainers (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(80)  NOT NULL,
    email VARCHAR(120) NOT NULL,
    CONSTRAINT uq_trainers_email UNIQUE (email)
);

CREATE TABLE courses (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    code       VARCHAR(12)  NOT NULL,
    title      VARCHAR(120) NOT NULL,
    trainer_id INT          NOT NULL,
    CONSTRAINT uq_courses_code   UNIQUE (code),
    CONSTRAINT fk_courses_trainer FOREIGN KEY (trainer_id) REFERENCES trainers(id)
);

CREATE TABLE sessions (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    course_id  INT  NOT NULL,
    starts_on  DATE NOT NULL,
    seats      INT  NOT NULL,
    CONSTRAINT fk_sessions_course FOREIGN KEY (course_id) REFERENCES courses(id),
    CONSTRAINT ck_sessions_seats  CHECK (seats > 0)
);

CREATE TABLE students (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(80)  NOT NULL,
    email VARCHAR(120) NOT NULL,
    CONSTRAINT uq_students_email UNIQUE (email)
);

CREATE TABLE enrolments (
    student_id INT NOT NULL,
    session_id INT NOT NULL,
    status     VARCHAR(12)   NOT NULL DEFAULT 'booked',
    fee_paid   DECIMAL(10,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (student_id, session_id),
    CONSTRAINT fk_enrol_student FOREIGN KEY (student_id) REFERENCES students(id),
    CONSTRAINT fk_enrol_session FOREIGN KEY (session_id) REFERENCES sessions(id),
    CONSTRAINT ck_enrol_status  CHECK (status IN ('booked', 'attended', 'cancelled'))
);

Explanation

Notice what the design decides on your behalf, permanently:

• courses.trainer_id NOT NULL says a course cannot exist without a trainer. If the business allows unassigned courses, that must be nullable instead.
• enrolments.fee_paid lives on the pairing, because it is a fact about this student on this session - not about the student and not about the session.
• The CHECK on status means an application typo can never create a fourth status value.

Every one of those is a business rule expressed in DDL. Rules you leave out of the schema will eventually be broken by some script nobody remembers writing.

Naming conventions

Important rules

• One entity per table. If a table needs a "type" column that changes which other columns are meaningful, you probably have two entities.
• One fact per column. No comma separated lists, no notes column holding structured data.
• Attributes of a relationship belong in the junction table.
• Never store a value that can be derived, unless you have measured a need to and have a plan to keep it correct.
• Avoid reserved words as identifiers: order, user, group, desc.

Common mistakes

• Designing from the screen outwards - one table per form - rather than from the entities.
• Wide tables with dozens of nullable columns that only apply to some rows.
• Repeating groups: phone1, phone2, address_line_1..5.
• Storing computed totals that drift out of step with the rows they summarise.
• Mixing naming conventions across tables in the same schema.

Best practices

• Write the business rules as sentences first, then translate each into a table, a key or a constraint.
• Give every table a primary key and every foreign key an index.
• Add created_at and updated_at to entity tables; you will want them within a month.
• Keep the schema in migration files under version control, reviewed like code.
• Design for the queries you know you need, then normalise - not the other way round.

Practice

1. Model a library: books, copies, members, loans. Which relationship needs a junction table, and which does not?
2. Which column in your loans table records a fact about the pairing rather than about either parent?
3. Write three business rules for the library as CHECK or UNIQUE constraints.