Data Modeling

Personal Catalogue: Django & PostgreSQL

Chapter 2 · Data Modeling

Chapter 1 got a Django project genuinely talking to a live PostgreSQL database. This chapter designs the real models that database will actually store — 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 with Django:

One model per item type — a real, fully normalized Book model with an author field, a real MusicCD model with an artist field, and so on. Every field is exactly the right shape for its type, with no unused columns anywhere.
One shared Item model — a single model covering every item type, with a generic creator field 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 models, answering that means a real query against each one and combining the results in Python. With one shared model, it's a single Django ORM filter() call. Given the real time pressure this project starts with, the shared-model design is the one that lets the search feature (Chapter 7) 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 field would genuinely apply to every row. The shared-model design accepts a few fields that are meaningless for some rows (a DVD's own creator would always be blank) 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.

Model 1: ItemType

Rather than hard-coding "book", "cd", "dvd", "bluray" with Django's own TextChoices pattern, this course uses a small, real database-backed lookup model instead:

# catalogue/models.py
from django.db import models

class ItemType(models.Model):
    name = models.CharField(max_length=20, unique=True)

    def __str__(self):
        return self.name

A TextChoices class would work for a fixed, never-changing list — but this collection is never really fixed. Adding "vinyl" or "video game" later with a lookup model is one new row created through the admin; adding it to a hard-coded choices list means editing the model's own source code and shipping a new migration just to add a value. The lookup model costs one extra join later, in exchange for that flexibility staying cheap and entirely data-driven.

Model 2: Item

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

class Item(models.Model):
    item_type = models.ForeignKey(ItemType, on_delete=models.PROTECT)
    title = models.CharField(max_length=255)
    creator = models.CharField(max_length=255, blank=True)
    format_detail = models.CharField(max_length=100, blank=True)
    release_year = models.PositiveSmallIntegerField(null=True, blank=True)
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.title

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

Field	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: Django's own auto_now_add fills it in automatically at insert time, giving a real "recently added" ordering with zero extra application code.

on_delete=models.PROTECT on item_type is a deliberate choice, not the Django default: it stops an ItemType from ever being deleted while items of that type still exist, raising a real error instead of silently cascading the deletion (or worse, silently leaving orphaned rows) — a genuinely safer default for a lookup table whose values items actively depend on.

Tags & the Many-to-Many

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:

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True)

    def __str__(self):
        return self.name

class Item(models.Model):
    # ... fields from above ...
    tags = models.ManyToManyField(Tag, blank=True, related_name='items')

Django Generates the Join Table for You

In the PHP + MySQL variant of this project (catalog-php1), the join table connecting items and tags — item_tags, with its own composite primary key preventing duplicate assignments — had to be written by hand as a real CREATE TABLE statement. Here, models.ManyToManyField generates that exact join table automatically the moment a migration runs, including its own uniqueness constraint on the pairing. This is one of the genuinely real conveniences of choosing an ORM-based framework over raw SQL — at the real cost that customizing that generated join table later (adding a "date tagged" column, say) needs Django's own through= model syntax rather than a simple column addition, since the table is no longer one this project's own code defines directly.

The Full Schema, in One Picture

┌──────────────┐ ┌──────────────────┐ ┌───────────┐ │ ItemType │ │ Item │ │ Tag │ ├──────────────┤ ├──────────────────┤ ├───────────┤ │ id (PK) │◄───────│ item_type_id (FK) │ │ id (PK) │ │ name │ │ id (PK) │ │ name │ └──────────────┘ │ title │ └─────▲─────┘ │ creator │ │ │ format_detail │ ┌─────┴──────────────┐ │ release_year │◄───────│ item_tag (auto-gen) │ │ notes │ ├─────────────────────┤ │ created_at │ │ item_id (FK) │ └──────────────────┘ │ tag_id (FK) │ └─────────────────────┘

Running the Migration

Django's own migration system turns these three model classes into real PostgreSQL tables:

python manage.py makemigrations catalogue
python manage.py migrate

makemigrations reads the model classes and writes a real migration file describing the tables, columns, and constraints needed; migrate then applies that file as real SQL against the live PostgreSQL database. Seed the four real item types this course needs before moving on, either through the Django shell or — once Chapter 3 sets it up — through the admin interface directly:

python manage.py shell
>>> from catalogue.models import ItemType
>>> for name in ['book', 'cd', 'dvd', 'bluray']:
...     ItemType.objects.get_or_create(name=name)

get_or_create Avoids a Real Duplicate-Seeding Bug

Running a plain ItemType.objects.create(name=name) loop a second time — say, by accidentally re-running the seed step — would insert four duplicate rows, since nothing stops it. get_or_create() checks for an existing row with the given field first, only inserting when one genuinely doesn't exist yet, making the seed step safe to run more than once by accident without corrupting the lookup table.

Hands-On Exercises

Exercise 1

Write all three model classes from this chapter in catalogue/models.py, run makemigrations and migrate, and confirm the real PostgreSQL tables were created using psql's own \dt command.

📄 View solution

Exercise 2

Explain, in your own words, why a shared Item model with a generic creator field was chosen over four separate per-type models, and name the one real feature that decision makes simpler.

📄 View solution

Exercise 3

Using the Django shell, create one book Item, create one Tag, attach the tag to the book via item.tags.add(tag), then confirm the relationship by printing item.tags.all().

📄 View solution

Chapter 2 Quick Reference

Item — one shared model for every item type, with a generic creator field standing in for author/artist/director
ItemType — a lookup model (not TextChoices) so new item types can be added as data, with no code change or migration needed
Tag + ManyToManyField — a real many-to-many relationship, with Django auto-generating the join table and its own uniqueness constraint
Design trade-off — a slightly less "pure" shared-model schema, chosen specifically because it keeps the app's own core search feature a single ORM query
Safety note — on_delete=PROTECT on item_type, and get_or_create() for safe, re-runnable seeding
Next chapter: the Django admin as a fast, ready-made manual entry tool for this real schema