Data Modeling

Personal Catalogue: PHP & MySQL

Chapter 2 · Data Modeling

Chapter 1 got a connection open and a database created. This chapter designs the real tables that connection will actually talk to — a schema that has to hold four genuinely different kinds of item (books, CDs, DVDs, Blu-rays), let books carry an arbitrary number of tags, and stay fast and simple to query given this project's own real deadline.

The Shape of the Problem

A book has an author. A CD has an artist. A DVD or Blu-ray has a director. All four have a title, a format, and a year. There are two honest ways to model that in MySQL:

One table per item type — a real, fully normalized books table with an author column, a real music_cds table with an artist column, and so on. Every field is exactly the right shape for its type, with no unused columns anywhere.
One shared items table — a single table covering every item type, with a generic creator column standing in for author/artist/director depending on the row's own type.

This course uses the second approach, and it's worth being honest about why: the app's own core real question is "do I already own this?" — a single search across every item type at once. With four separate tables, answering that means a real four-way UNION query every single time. With one shared table, it's a single WHERE clause. Given the real time pressure this project starts with, the shared-table design is the one that lets the search feature (Chapter 6) stay genuinely simple.

This Is a Real, Deliberate Trade-off

A fully normalized per-type schema is arguably "more correct" in a formal database-design sense — every column would genuinely apply to every row. The shared-table design accepts a few columns that are meaningless for some rows (a DVD's own author would always be NULL) in exchange for a dramatically simpler search query. That's the right call here specifically because search-across-everything is this app's own central feature — it wouldn't necessarily be the right call for every project.

Table 1: item_types

Rather than hard-coding "book", "cd", "dvd", "bluray" as a MySQL ENUM, this course uses a small lookup table instead:

CREATE TABLE item_types (
    id TINYINT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO item_types (id, name) VALUES
    (1, 'book'),
    (2, 'cd'),
    (3, 'dvd'),
    (4, 'bluray');

An ENUM would work for a fixed, never-changing list — but this collection is never really fixed. Adding "vinyl" or "video game" later with a lookup table is one INSERT statement; adding it to an ENUM means an ALTER TABLE that MySQL has to rewrite the underlying column definition for. The lookup table costs one extra join later, in exchange for that flexibility staying cheap.

Table 2: items

The main table. Every item in the catalogue — regardless of type — is one row here:

CREATE TABLE items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_type_id TINYINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    creator VARCHAR(255),
    format_detail VARCHAR(100),
    release_year SMALLINT UNSIGNED,
    notes TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_type_id) REFERENCES item_types(id)
);

A few of these columns are deliberately generic, standing in for something different depending on the item's own type:

Column	Book	CD	DVD / Blu-ray
`creator`	Author	Artist	Director
`format_detail`	Hardcover / paperback	Single / album	Region code
`release_year`	Publication year	Release year	Release year

release_year is deliberately nullable — the exact publication year isn't always known or worth tracking down on day one, and a real project under time pressure shouldn't make a field mandatory just because it would be nice to have. created_at, on the other hand, is genuinely useful and free: MySQL fills it in automatically, giving a real "recently added" ordering with zero extra application code.

Table 3 & the Join: tags and item_tags

Only books get tags in this project's own spec, but the tags themselves — Python, Web Development, Programming — are reusable across many books, and any one book can carry several. That's a genuine many-to-many relationship, which MySQL models with a real join table sitting between the two sides:

CREATE TABLE tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE item_tags (
    item_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (item_id, tag_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

item_tags has no id column of its own — its composite primary key, (item_id, tag_id), is exactly the pairing it exists to record, and it also does useful work as a constraint: MySQL will reject an attempt to attach the exact same tag to the exact same item twice, since that pairing would violate the primary key's own uniqueness.

Why item_tags Isn't Restricted to Books Only

The join table doesn't actually stop a CD or DVD from being tagged — nothing in this schema enforces "tags only apply to item_type_id = 1". That's a deliberate simplification, not an oversight: enforcing it at the database level would need either a trigger or an application-level check, and the real spec only ever asks for tags on books. Leaving the schema itself untyped here costs nothing today and quietly allows the feature to extend later if that requirement ever changes.

The Full Schema, in One Picture

┌──────────────┐ ┌──────────────────┐ ┌───────────┐ │ item_types │ │ items │ │ tags │ ├──────────────┤ ├──────────────────┤ ├───────────┤ │ id (PK) │◄───────│ item_type_id (FK) │ │ id (PK) │ │ name │ │ id (PK) │ │ name │ └──────────────┘ │ title │ └─────▲─────┘ │ creator │ │ │ format_detail │ ┌─────┴──────┐ │ release_year │◄───────│ item_tags │ │ notes │ ├────────────┤ │ created_at │ │ item_id (FK) │ └──────────────────┘ │ tag_id (FK) │ └────────────┘

Foreign Keys Need an Engine That Supports Them

FOREIGN KEY constraints only actually work under MySQL's InnoDB storage engine — the modern default for a fresh install, but worth checking if this database ever gets migrated onto an older or differently-configured server. Under the older MyISAM engine, the FOREIGN KEY clauses above are silently accepted as valid syntax but never actually enforced, which would let orphaned rows creep into items or item_tags with no error raised anywhere.

Hands-On Exercises

Exercise 1

Run all four CREATE TABLE statements from this chapter against the catalogue database created in Chapter 1, in the correct order (the tables with foreign keys must be created after the tables they reference).

📄 View solution

Exercise 2

Explain, in your own words, why a shared items table with a generic creator column was chosen over four separate per-type tables, and name the one real feature that decision makes simpler.

📄 View solution

Exercise 3

Write and run a single SQL INSERT statement that adds one book, one tag, and one row into item_tags linking the two together — then write a SELECT with a join that lists the book's title alongside its tag's name.

📄 View solution

Chapter 2 Quick Reference

items — one shared table for every item type, with a generic creator column standing in for author/artist/director
item_types — a lookup table (not an ENUM) so new item types can be added with a single INSERT
tags + item_tags — a real many-to-many relationship, with item_tags' own composite primary key preventing duplicate tag assignments
Design trade-off — a slightly less "pure" shared-table schema, chosen specifically because it keeps the app's own core search feature a single query
Engine note — foreign keys require InnoDB; they're silently unenforced under MyISAM
Next chapter: Building the CRUD Pages — add, edit, and delete an item against this real schema