Skip to content

Tutorial 03 — Domain Design, ORM and the selectors/services architecture

This tutorial walks you through the full cycle from database design to a finished application: the DOMAIN→SCHEMA methodology → relationship types → Django models → migrations → QuerySet API → selectors/services architecture → thin views.

The project is a personal notes manager with all types of relationships between tables.


Contents

Theory (read before the code) - 01 · DOMAIN → SCHEMA — a 7-step methodology - 02 · RELATIONSHIP TYPES — OneToOne, ForeignKey, ManyToMany - 03 · on_delete — deletion strategies - 04 · DJANGO ORM — QuerySet API - 05 · THE N+1 PROBLEM — select_related and prefetch_related - 06 · transaction.atomic — atomic operations - 07 · APPLICATION LAYERS — the selectors/services architecture

Step-by-step implementation 1. Step 0 — From domain to schema 2. Step 1 — Designing the domain 3. Step 2 — Entities and relationships 4. Step 3 — ER diagram 5. Step 4 — Django models 6. Step 5 — Migrations 7. Step 6 — QuerySet queries in the shell 8. Step 7 — selectors.py 9. Step 8 — services.py 10. Step 9 — Thin views


01 · DOMAIN → SCHEMA

"Design before code." Mistake #1 is starting with class Note(models.Model) and adding fields "on the fly". A month later — refactoring the schema = a complex migration that is hard to roll back in prod. 30 minutes of design = weeks of saved time.

The 7-step methodology

# Step What you do Example
1 DOMAIN Describe in words what you are building "A user has notebooks. Notebooks contain notes. Notes have tags and reminders."
2 ENTITIES Find the nouns in the description User, Notebook, Note, Tag, Reminder
3 ATTRIBUTES What do we know about each entity? Note → title, content, priority, is_pinned, is_archived
4 RELATIONSHIPS How are the entities related? 1:1 User↔Profile · 1:N Notebook→Notes · M:N Note↔Tag
5 NORMALIZATION Remove duplication author_name in Note? — No, only an FK to User
6 on_delete What happens when the parent is deleted? CASCADE / SET_NULL / PROTECT
7 DJANGO MODEL Only now do we write models.py ← Typical mistake: starting here

Where the "truth" lives

Normalization — every fact is stored in exactly one place.

❌ DENORMALIZED SCHEMA (duplication):
NOTE table:
  author_name VARCHAR(150)  ← duplication of the user's name!
  author_email VARCHAR(254) ← and the email too!

Problem:
  1. The user changes their name → you must UPDATE all their notes
  2. Inconsistency: note.author_name ≠ user.username → DATA INCONSISTENCY
  3. Deleting the user → orphan records without an author

✅ NORMALIZED SCHEMA:
NOTE table:
  user_id BIGINT FK → auth_user  ← one fact: this note belongs to the user with this id

The user's name? → note.user.username (JOIN)
The user's email? → note.user.email (JOIN)
One source of truth → no inconsistency

02 · Relationship Types

1:1 — OneToOneField

USER ──────────────► USER_PROFILE
  id PK                 user_id UNIQUE FK
class UserProfile(models.Model):
    user = models.OneToOneField(
        User,
        on_delete=models.CASCADE,
        related_name='profile',
    )
    display_name = models.CharField(max_length=60, blank=True)
    timezone     = models.CharField(max_length=40, default='UTC')
    avatar_url   = models.URLField(blank=True)

    def __str__(self):
        return f'Profile({self.user.username})'

The SQL Django generates:

CREATE TABLE "hello_app_userprofile" (
    "id"           bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    "user_id"      bigint NOT NULL UNIQUE REFERENCES "auth_user" ("id") ON DELETE CASCADE,
    "display_name" varchar(60) NOT NULL DEFAULT '',
    "timezone"     varchar(40) NOT NULL DEFAULT 'UTC',
    "avatar_url"   varchar(200) NOT NULL DEFAULT ''
);

Reverse — access from User:

user = User.objects.get(username='alice')
user.profile              # → UserProfile (not a QuerySet! UNIQUE FK → a single object)
user.profile.timezone     # → 'Europe/Kyiv'

When to use 1:1? - "Vertical partitioning": 5 basic fields in User, 20 optional ones in Profile - Different access: User changes often, Profile rarely → a separate QuerySet - Different permissions: view_user without view_userprofile


1:N — ForeignKey

NOTEBOOK ────────── NOTE
  id PK              id PK
  user_id FK         notebook_id NULL FK ← the FK lives on the "Many" side
  title              title
                     content
class Note(models.Model):
    notebook = models.ForeignKey(
        'Notebook',                  # ← a string avoids circular imports
        on_delete=models.SET_NULL,   # Deleting the notebook → NULL (not CASCADE!)
        null=True,                   # NULL is required with SET_NULL
        blank=True,                  # Form: the field is optional
        related_name='notes',        # notebook.notes.all() → QuerySet of notes
    )

KEY RULE: the FK lives on the "Many" side.

WRONG way to think:            RIGHT way to think:
Notebook has a list of notes    Note belongs to Notebook
→ Notebook.notes = [...]    → Note.notebook_id = FK
→ a JOIN will happen         → Notebook.notes.all() = reverse

Notebook does NOT store a list of Note IDs!
That would violate normal form and complicate the JOIN.

Reverse — access from the "One" side:

notebook = Notebook.objects.get(id=1)
notebook.notes.all()                    # ← related_name='notes' → QuerySet
notebook.notes.filter(is_pinned=True)   # Filtering
notebook.notes.count()                  # Count

note = Note.objects.get(id=1)
note.notebook                           # ← FK attr → Notebook object (or None)
note.notebook_id                        # ← raw integer FK without a JOIN
note.notebook.title                     # ← JOIN: SELECT ... WHERE notebook_id=...


M:N — ManyToManyField

NOTE ────── NOTE_TAGS (junction) ────── TAG
  id PK       note_id FK                 id PK
  title       tag_id FK                  name
              UNIQUE(note_id, tag_id)
class Note(models.Model):
    tags = models.ManyToManyField(
        'Tag',
        blank=True,              # A note may have no tags
        related_name='notes',    # tag.notes.all() → notes with this tag
    )
    # Django automatically creates the junction table: hello_app_note_tags
    # No need to describe a NoteTag model by hand

Operations with M:N:

note = Note.objects.get(id=1)
tag_py = Tag.objects.get(name='python')
tag_dj = Tag.objects.get(name='django')

# Add tags
note.tags.add(tag_py, tag_dj)     # INSERT INTO note_tags (note_id, tag_id)

# The full list
note.tags.all()                    # SELECT * FROM tags JOIN note_tags ...

# Remove a specific tag
note.tags.remove(tag_py)           # DELETE FROM note_tags WHERE ...

# Replace all tags
note.tags.set([tag_dj])           # DELETE old ones + INSERT new ones

# Clear all
note.tags.clear()                  # DELETE FROM note_tags WHERE note_id=...

# Reverse — notes with a tag:
tag_py.notes.all()                 # SELECT * FROM notes JOIN note_tags WHERE tag_id=...


03 · on_delete

What does Django do when a "parent" record is deleted? This is a business decision, not a technical one. Think about the user's data!

# CASCADE — delete child records together with the parent
class Reminder(models.Model):
    note = models.ForeignKey(Note, on_delete=models.CASCADE, related_name='reminders')
    # Deleting a Note → all Reminders of that Note are deleted
    # Logic: a Reminder without a Note is meaningless → CASCADE is the right choice

# SET_NULL — the FK becomes NULL, the child record remains
class Note(models.Model):
    notebook = models.ForeignKey(
        Notebook, on_delete=models.SET_NULL,
        null=True, blank=True,  # ← null=True is REQUIRED with SET_NULL
    )
    # Deleting a Notebook → Note.notebook = NULL
    # Logic: the note must be kept! Only the notebook is deleted.

# PROTECT — forbids deletion if there are child records
class Note(models.Model):
    tag = models.ForeignKey(Tag, on_delete=models.PROTECT)
    # Deleting a Tag → ProtectedError (exception)
    # Logic: don't allow deleting a tag while it is in use

# RESTRICT — similar to PROTECT but allows a cascade from a higher level
# Complex hierarchies, rarely used

# SET_DEFAULT — the FK becomes the default value
class Note(models.Model):
    notebook = models.ForeignKey(
        Notebook, on_delete=models.SET_DEFAULT,
        default=1,  # ← pk of the default notebook
    )

# DO_NOTHING — leaves an orphan record (the FK points to a nonexistent id)
# AVOID: leads to IntegrityError or dirty data

The on_delete decision matrix

If you delete... What do we want for the children? on_delete
Note Delete the Reminders (they make no sense without the Note) CASCADE
Notebook Keep the Notes (a note matters more than the notebook) SET_NULL
User Delete all Notes (the content belongs to the user) CASCADE
Tag Forbid deletion (the tag is in use) PROTECT

04 · Django ORM

ORM (Object-Relational Mapping) — you write Python, Django generates SQL.

QuerySet — lazy objects

# QuerySet is LAZY: SQL is not executed until the data is needed
qs = Note.objects.filter(user=alice)   # ← NOTHING happened in the DB!
qs = qs.filter(is_pinned=True)         # ← Only refining the query
qs = qs.order_by('-priority')          # ← Still nothing in the DB

# SQL IS EXECUTED here — when we iterate, count, convert:
notes = list(qs)           # ← SELECT NOW!
count = qs.count()         # ← SELECT COUNT(*)
first = qs.first()         # ← SELECT ... LIMIT 1
exists = qs.exists()       # ← SELECT 1 ... LIMIT 1 (very fast)
for note in qs: ...        # ← SELECT and iteration
note = qs.get(pk=1)        # ← SELECT WHERE pk=1

# ADVANTAGE OF LAZINESS:
# You can pass a QuerySet to the template → SQL runs only during rendering
# You can chain filters: qs.filter(A).filter(B) = one SELECT with two WHEREs

Filtering — lookups

# Exact equality:
Note.objects.filter(priority=3)
# SQL: WHERE priority = 3

# Comparison:
Note.objects.filter(priority__gt=1)    # greater than → > 1
Note.objects.filter(priority__gte=2)   # greater or equal → >= 2
Note.objects.filter(priority__lt=3)    # less than → < 3
Note.objects.filter(priority__lte=2)   # less or equal → <= 2

# Strings:
Note.objects.filter(title__exact='Test')          # = 'Test' (case-sensitive)
Note.objects.filter(title__iexact='test')          # = 'test' (case-insensitive)
Note.objects.filter(title__contains='Django')      # LIKE '%Django%'
Note.objects.filter(title__icontains='django')     # ILIKE '%django%'
Note.objects.filter(title__startswith='Hello')     # LIKE 'Hello%'
Note.objects.filter(title__endswith='.md')         # LIKE '%.md'

# NULL:
Note.objects.filter(notebook__isnull=True)         # IS NULL
Note.objects.filter(notebook__isnull=False)        # IS NOT NULL

# Lists:
Note.objects.filter(priority__in=[1, 2])           # IN (1, 2)
Note.objects.exclude(priority__in=[3, 4])          # NOT IN (3, 4)

# Dates:
Note.objects.filter(created_at__year=2026)
Note.objects.filter(created_at__date=date(2026, 6, 13))
Note.objects.filter(created_at__gte=datetime(2026, 1, 1))

# FK:
Note.objects.filter(notebook=my_notebook)          # WHERE notebook_id = X
Note.objects.filter(notebook__title='Work')        # JOIN: WHERE notebook.title = 'Work'
Note.objects.filter(notebook__user=request.user)   # 2 JOIN levels

# M:N:
Note.objects.filter(tags=tag_python)               # notes with the python tag
Note.objects.filter(tags__name='python')           # JOIN on Tag
Note.objects.filter(tags__name__icontains='py')    # JOIN + LIKE

Q objects — complex conditions

from django.db.models import Q

# OR condition:
Note.objects.filter(Q(title__icontains='django') | Q(content__icontains='django'))
# SQL: WHERE (title ILIKE '%django%' OR content ILIKE '%django%')

# AND (by default several filters = AND):
Note.objects.filter(Q(is_pinned=True) & Q(priority=3))
# SQL: WHERE is_pinned = TRUE AND priority = 3

# NOT:
Note.objects.filter(~Q(is_archived=True))
# SQL: WHERE NOT is_archived = TRUE  (= WHERE is_archived = FALSE)

# Combining:
Note.objects.filter(
    Q(user=alice) | Q(group__in=alice_groups)
).filter(
    is_archived=False
)
# SQL: WHERE (user_id = 1 OR group_id IN (...)) AND is_archived = FALSE

Annotations and aggregates

from django.db.models import Count, Avg, Max, Min, Sum, F

# Count — the number of related objects
Notebook.objects.annotate(note_count=Count('notes'))
# SQL: SELECT *, COUNT(notes.id) AS note_count FROM notebooks LEFT JOIN notes ...
# notebook.note_count is available on every object

# Filtered Count
Notebook.objects.annotate(
    active_count=Count('notes', filter=Q(notes__is_archived=False))
)

# Aggregate — one value for the whole QuerySet
Note.objects.aggregate(avg_priority=Avg('priority'))
# → {'avg_priority': 1.8}

Note.objects.filter(user=alice).aggregate(
    total=Count('id'),
    pinned=Count('id', filter=Q(is_pinned=True)),
    max_priority=Max('priority'),
)
# → {'total': 42, 'pinned': 5, 'max_priority': 3}

# F objects — a reference to a field in SQL (without loading it into Python)
Note.objects.filter(views_count__gt=F('priority') * 100)
# SQL: WHERE views_count > priority * 100
# Without F: you would have to load all objects into Python and filter in a loop!

# update with F():
Note.objects.all().update(views_count=F('views_count') + 1)
# SQL: UPDATE ... SET views_count = views_count + 1
# An atomic operation! No race condition.

values() and values_list()

# Returns dictionaries instead of objects (faster for large QuerySets)
Note.objects.values('title', 'priority')
# → [{'title': 'Test', 'priority': 1}, ...]

# Returns tuples (even faster)
Note.objects.values_list('title', 'priority')
# → [('Test', 1), ('Django', 2), ...]

# flat=True: one column → a flat list
Note.objects.values_list('id', flat=True)
# → [1, 2, 3, 4, ...]

# Save all ids into a list:
note_ids = list(Note.objects.filter(user=alice).values_list('id', flat=True))

order_by, distinct, only, defer

# Sorting:
Note.objects.order_by('priority')           # ASC
Note.objects.order_by('-priority')          # DESC
Note.objects.order_by('-is_pinned', '-priority', '-updated_at')  # Multiple

# Remove duplicates (with a JOIN):
Note.objects.filter(tags__name='python').distinct()

# Load only certain fields (optimization):
Note.objects.only('id', 'title', 'priority')   # Other fields → an additional SELECT on access
Note.objects.defer('content')                   # All fields EXCEPT content

# Limiting (LIMIT / OFFSET):
Note.objects.all()[:10]                        # LIMIT 10
Note.objects.all()[10:20]                      # LIMIT 10 OFFSET 10
Note.objects.all()[0]                          # LIMIT 1 (= .first() but not None)

05 · The N+1 Problem

The most common performance bug in Django — Django makes 1 + N SQL queries instead of 1.

The connection with lazy evaluation

N+1 happens precisely because a QuerySet is lazy. The student already knows that for note in notes: materializes one SELECT. But as soon as note.notebook appears in the template, Django does not know that notebook will be needed for every note, and makes a separate SELECT for each one:

{% for note in notes %}
    {{ note.notebook.title }}   ← for each note: SELECT FROM notebooks WHERE id=?
{% endfor %}

50 notes → the template "asks for" the notebook 50 times → 51 SQL queries instead of the expected one.

This is not a Django bug — it is a natural consequence of lazy loading. The solution is to tell Django explicitly in advance which relationships will be needed.

What N+1 is

# 50 notes in the database. Each has a ForeignKey to Notebook.
notes = Note.objects.all()          # Query 1: SELECT * FROM notes  ← 50 rows

for note in notes:
    print(note.notebook.title)       # Query 2, 3, 4, ..., 51!
    # Each note.notebook.title → a SEPARATE SELECT FROM notebooks WHERE id=...
    # 50 notes → 50 extra queries → 51 TOTAL!
# select_related: Django does a JOIN → 1 query instead of 51
notes = Note.objects.select_related('notebook').all()
# SQL: SELECT notes.*, notebooks.* FROM notes LEFT JOIN notebooks ON notebook_id=notebooks.id

for note in notes:
    print(note.notebook.title)   # ← the data is already in memory, no SQL at all!
# prefetch_related: 2 queries instead of 1 + N
notes = Note.objects.prefetch_related('tags').all()
# SQL query 1: SELECT * FROM notes
# SQL query 2: SELECT tags.* FROM tags JOIN note_tags WHERE note_id IN (1,2,3,...)
# Django joins the results in memory by itself

for note in notes:
    for tag in note.tags.all():   # ← from the cache, no SQL at all!
        print(tag.name)

Combining

# A real example: the note_list view
def get_user_notes(user, *, archived=False):
    return Note.objects.filter(
        user=user,
        is_archived=archived,
    ).select_related(
        'notebook',   # FK → JOIN (1 query)
        'user',       # FK → JOIN (already there, but explicit)
    ).prefetch_related(
        'tags',       # M:N → 2 queries total
    ).order_by('-is_pinned', '-priority', '-updated_at')

# TOTAL: 2 SQL queries for any number of notes ✓
# WITHOUT optimization: 1 + N + N SQL (for N notes)

How to detect N+1: Django Debug Toolbar

pip install django-debug-toolbar
# settings.py:
INSTALLED_APPS = ['debug_toolbar', ...]
MIDDLEWARE = ['debug_toolbar.middleware.DebugToolbarMiddleware', ...MIDDLEWARE]
INTERNAL_IPS = ['127.0.0.1']

# urls.py:
if settings.DEBUG:
    import debug_toolbar
    urlpatterns = [path('__debug__/', include(debug_toolbar.urls))] + urlpatterns

In the browser → a side panel shows all SQL queries with duplicates highlighted.


06 · transaction.atomic

An atomic operation: either ALL changes are saved, or NONE.

The problem without atomic

def create_note(user, title, tag_ids):
    note = Note.objects.create(user=user, title=title)
    # ← INSERT INTO note → id=42

    # AN ERROR HERE (for example, the tag does not exist):
    tags = Tag.objects.filter(id__in=tag_ids, user=user)
    note.tags.set(tags)   # ← An exception because of a wrong tag_id!

    # DATABASE STATE:
    # The Note with id=42 exists (the INSERT already happened)
    # But the tags are not attached
    # INCONSISTENT STATE! An "orphan" without tags

The solution — transaction.atomic

from django.db import transaction

def create_note(user, title, tag_ids):
    with transaction.atomic():
        note = Note.objects.create(user=user, title=title)
        # ← While inside the atomic block → BEGIN TRANSACTION

        tags = Tag.objects.filter(id__in=tag_ids, user=user)
        note.tags.set(tags)
        # ← If an exception occurs → ROLLBACK of the whole block
        # The Note is NOT saved in the DB!

    # ← If everything is OK → COMMIT
    return note

Nested atomic blocks — savepoints

def complex_operation():
    with transaction.atomic():      # ← BEGIN
        do_operation_1()            # ← INSERT ...

        with transaction.atomic():  # ← SAVEPOINT sp_1
            do_operation_2()        # ← INSERT ...
            # If an exception occurs here → ROLLBACK TO sp_1
            # operation_1 remains!

        do_operation_3()            # ← INSERT ...
    # ← COMMIT (if everything is OK)

@transaction.atomic as a decorator

@transaction.atomic
def create_notebook(user, title, is_default=False):
    """
    Business rule: a user has only ONE default notebook.
    These two queries must be atomic:
    1. Reset the previous default
    2. Set the new one
    """
    if is_default:
        Notebook.objects.filter(user=user, is_default=True).update(is_default=False)
        # ← If an exception occurs here → there will be no "no default at all" situation
    return Notebook.objects.create(user=user, title=title, is_default=is_default)
    # ← Both operations, or both are rolled back

07 · Application Layers

Splitting responsibility between layers is a key architectural practice.

The problem: a "Fat View"

# ❌ ANTIPATTERN: all the logic in the view
def note_list(request):
    # Does the view know about QuerySet optimization? Not its business!
    notes = Note.objects.filter(
        user=request.user,
        is_archived=False,
    ).select_related('notebook').prefetch_related('tags')

    # Does the view know about business rules? Not its business!
    if request.GET.get('q'):
        notes = notes.filter(
            Q(title__icontains=request.GET['q']) |
            Q(content__icontains=request.GET['q'])
        )

    # If this logic is needed in an API endpoint or a Celery task → do we copy it?!
    return render(request, 'note_list.html', {'notes': notes})

The solution: Thin View + selectors/services

HTTP REQUEST
     │
     ▼
┌────────────────────────────────────────────────────────────────────────┐
│  views.py                                                               │
│  ✓ Parses request parameters: request.GET.get('q', '')                 │
│  ✓ Calls a selector or a service                                        │
│  ✓ render or redirect                                                   │
│  ✗ Does NOT contain QuerySets                                           │
│  ✗ Does NOT contain business rules                                      │
└────────────────────────────────────┬───────────────────────────────────┘
                    READ ↓           │          WRITE ↓
                    ▼                │          ▼
┌───────────────────────────┐        │  ┌────────────────────────────────┐
│  selectors.py              │        │  │  services.py                   │
│  ✓ SELECT with filters     │        │  │  ✓ CREATE / UPDATE / DELETE    │
│  ✓ select_related          │        │  │  ✓ transaction.atomic()        │
│  ✓ prefetch_related        │        │  │  ✓ business rules              │
│  ✓ annotate                │        │  │  ✗ NO render / redirect        │
│  ✗ NO INSERT/UPDATE/DELETE │        │  │  ✗ NO QuerySets unless needed  │
└────────────────┬───────────┘        │  └───────────────────┬────────────┘
                 └────────────────────┘                       │
                                  PostgreSQL ◄─────────────────┘

The responsibility matrix

Layer Does Does NOT do
models.py Structure, fields, constraints, __str__, Meta Business logic, HTTP
selectors.py SELECT, filter, annotate, prefetch INSERT / UPDATE / DELETE
services.py CREATE / UPDATE / DELETE, transaction HTTP, render, redirect
views.py Parse the request, render, redirect QuerySets, business logic

The check rule: if you see Note.objects.filter(...) in views.py → move it to selectors.py.


Step 0 — From domain to schema

WRONG: coding the models right away.

RIGHT: first describe it in words:

"A user has a profile (1:1). A user creates notebooks (1:N). Notebooks contain notes (1:N). Notes have tags (M:N) and reminders (1:N). A note may have no notebook (SET_NULL)."

Now we pick out nouns = Entities and verbs = Relationships.


Step 1 — Designing the domain

What we build Description
Notes manager A user creates notes with tags and reminders
Notebooks Group notes (one note → one notebook or none)
Priority 1-3, affects sorting
Pinning is_pinned → at the top of the list
Archive is_archived → hidden from the main list

Step 2 — Entities and relationships

User (built-in Django auth_user)
  ↕ 1:1
UserProfile (display_name, timezone)

User
  ↓ 1:N
Notebook (title, color, is_default)

User
  ↓ 1:N
Note (title, content, priority, is_pinned, is_archived)
  ├── FK → Notebook (SET_NULL: a note without a notebook is OK)
  ├── M:N → Tag (via the note_tags junction)
  └── 1:N → Reminder (CASCADE: a reminder without a note is meaningless)

User
  ↓ 1:N
Tag (name, color, UniqueConstraint(user, name))

Step 3 — ER diagram

auth_user                    hello_app_userprofile
┌─────────────────────┐      ┌──────────────────────────────┐
│ id          bigint  │──1:1►│ id           bigint PK       │
│ username    varchar │      │ user_id      bigint UNIQUE FK│
│ email       varchar │      │ display_name varchar(60)     │
│ password    varchar │      │ timezone     varchar(40)     │
│ is_staff    bool    │      └──────────────────────────────┘
└──────┬──────────────┘
       │ owns 1:N               owns 1:N
       ▼                        ▼
hello_app_notebook          hello_app_tag
┌──────────────────┐         ┌──────────────────────────┐
│ id     bigint PK │         │ id      bigint PK        │
│ user_id bigint FK│         │ user_id bigint FK        │
│ title  varchar   │         │ name    varchar(50)      │
│ color  varchar(7)│         │ color   varchar(7)       │
│ is_default bool  │         └─────────┬────────────────┘
└──────┬───────────┘          UNIQUE(user_id, name)
       │ contains 1:N                  │
       ▼                               │ M:N via
hello_app_note (central)               │ hello_app_note_tags
┌───────────────────────────┐    ┌─────┴──────────────┐
│ id           bigint PK    │    │ note_id  bigint FK  │
│ user_id      bigint FK    │◄───│ tag_id   bigint FK  │
│ notebook_id  bigint NullFK│    └────────────────────┘
│ title        varchar(200) │    UNIQUE(note_id, tag_id)
│ content      text         │
│ priority     smallint     │    reminds 1:N
│ is_pinned    bool         │────►hello_app_reminder
│ is_archived  bool         │    ┌────────────────────┐
│ created_at   timestamp    │    │ id         bigint  │
│ updated_at   timestamp    │    │ note_id    bigint FK│
└───────────────────────────┘    │ remind_at  timestamp│
                                 │ is_sent    bool    │
                                 └────────────────────┘

Step 4 — Django models

hello_app/models.py:

from django.contrib.auth.models import User
from django.core.validators import MinValueValidator, MaxValueValidator
from django.db import models


class UserProfile(models.Model):
    user         = models.OneToOneField(User, on_delete=models.CASCADE, related_name='profile')
    display_name = models.CharField(max_length=60, blank=True)
    timezone     = models.CharField(max_length=40, default='UTC')

    def __str__(self):
        return f'Profile({self.user.username})'


class Tag(models.Model):
    user  = models.ForeignKey(User, on_delete=models.CASCADE, related_name='tags')
    name  = models.CharField(max_length=50)
    color = models.CharField(max_length=7, default='#6c757d')  # Hex color

    class Meta:
        ordering       = ['name']
        constraints    = [
            models.UniqueConstraint(fields=['user', 'name'], name='unique_user_tag'),
        ]

    def __str__(self):
        return f'#{self.name}'


class Notebook(models.Model):
    user       = models.ForeignKey(User, on_delete=models.CASCADE, related_name='notebooks')
    title      = models.CharField(max_length=100)
    color      = models.CharField(max_length=7, default='#6c757d')
    is_default = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['-is_default', 'title']

    def __str__(self):
        return self.title


class Note(models.Model):
    PRIORITY_LOW    = 1
    PRIORITY_MEDIUM = 2
    PRIORITY_HIGH   = 3
    PRIORITY_CHOICES = [
        (PRIORITY_LOW,    'Низький'),
        (PRIORITY_MEDIUM, 'Середній'),
        (PRIORITY_HIGH,   'Високий'),
    ]

    user     = models.ForeignKey(User, on_delete=models.CASCADE, related_name='notes')
    notebook = models.ForeignKey(
        Notebook, on_delete=models.SET_NULL,
        null=True, blank=True, related_name='notes',
    )
    tags = models.ManyToManyField(Tag, blank=True)

    title       = models.CharField(max_length=200)
    content     = models.TextField(blank=True)
    priority    = models.SmallIntegerField(
        choices=PRIORITY_CHOICES, default=PRIORITY_LOW,
        validators=[MinValueValidator(1), MaxValueValidator(3)],
    )
    is_pinned   = models.BooleanField(default=False)
    is_archived = models.BooleanField(default=False)
    created_at  = models.DateTimeField(auto_now_add=True)
    updated_at  = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-is_pinned', '-priority', '-updated_at']

    def __str__(self):
        return f'📌 {self.title}' if self.is_pinned else self.title

PRIORITY_CHOICES in notes_chat_app

This model uses 3 levels (1–3) for the learning project. notes_chat_app (the final project) has 4 levels: PRIORITY_URGENT = 4 ("🔴 Терміново"), PositiveSmallIntegerField instead of SmallIntegerField, and MaxValueValidator(4). In addition, notes_chat_app.Note has a group = ForeignKey(Group) field for group sharing (added in Step 5 — Authentication and security).

class Reminder(models.Model):
    note      = models.ForeignKey(Note, on_delete=models.CASCADE, related_name='reminders')
    remind_at = models.DateTimeField()
    is_sent   = models.BooleanField(default=False)

    class Meta:
        ordering = ['remind_at']

    def __str__(self):
        return f'Reminder({self.note.title}, {self.remind_at})'

Step 5 — Migrations

python manage.py makemigrations hello_app

Look at what Django generated:

python manage.py sqlmigrate hello_app 0001

The output shows the real SQL — useful for checking:

BEGIN;
CREATE TABLE "hello_app_tag" (
    "id"      bigint NOT NULL PRIMARY KEY,
    "user_id" bigint NOT NULL REFERENCES "auth_user" ("id"),
    "name"    varchar(50) NOT NULL,
    "color"   varchar(7) NOT NULL
);
CREATE UNIQUE INDEX "unique_user_tag" ON "hello_app_tag" ("user_id", "name");
-- ... and so on for each table
COMMIT;

python manage.py migrate

Changing a model after the first migration

# Adding a new field:
# views_count = models.PositiveIntegerField(default=0)

python manage.py makemigrations hello_app
# → Migrations for 'hello_app':
#     hello_app/migrations/0002_note_views_count.py
#       - Add field views_count to note

python manage.py migrate
# → Applying hello_app.0002_note_views_count... OK

Step 6 — QuerySets in the shell

python manage.py shell
from django.contrib.auth.models import User
from hello_app.models import Note, Notebook, Tag, Reminder

# Creating test data
alice = User.objects.create_user('alice', password='pass123')
nb = Notebook.objects.create(user=alice, title='Work', is_default=True)

# Tags
tag_py = Tag.objects.create(user=alice, name='python', color='#3776AB')
tag_dj = Tag.objects.create(user=alice, name='django', color='#092E20')

# Notes
note1 = Note.objects.create(
    user=alice, notebook=nb, title='Django ORM',
    content='Дуже потужний!', priority=Note.PRIORITY_HIGH, is_pinned=True
)
note1.tags.add(tag_py, tag_dj)

note2 = Note.objects.create(
    user=alice, title='Python tips', priority=Note.PRIORITY_MEDIUM
)
note2.tags.add(tag_py)

# Reminders
Reminder.objects.create(note=note1, remind_at='2026-07-01 09:00:00')

# Queries:
Note.objects.filter(user=alice).count()                    # → 2
Note.objects.filter(user=alice, is_pinned=True)            # → <QuerySet [<Note: 📌 Django ORM>]>
Note.objects.filter(tags=tag_py)                           # → both notes
Note.objects.filter(tags__name='django')                   # → note1

# With optimization:
notes = Note.objects.filter(user=alice)\
    .select_related('notebook')\
    .prefetch_related('tags')

for note in notes:
    print(f'{note.title} → {note.notebook} → {list(note.tags.all())}')
# Django ORM → Work → [#python, #django]
# Python tips → None → [#python]

# Annotate — the number of tags per note:
from django.db.models import Count
Note.objects.annotate(tag_count=Count('tags'))

Step 7 — selectors.py

hello_app/selectors.py:

from django.db.models import Count, Q

from .models import Note, Notebook, Tag


def get_user_notes(user, *, archived=False, notebook=None, tag=None, search=None):
    """
    The user's notes list with optimized JOINs.

    Keyword-only kwargs (after *) — protection against positional arguments:
    get_user_notes(alice, True) → TypeError (unclear that True = archived)
    get_user_notes(alice, archived=True) → OK, explicit
    """
    qs = Note.objects.filter(
        user=user,
        is_archived=archived,
    ).select_related(
        'notebook',   # FK: 1 JOIN, not N queries
    ).prefetch_related(
        'tags',       # M:N: a 2nd SELECT IN (...), not N queries
    )

    if notebook:
        qs = qs.filter(notebook=notebook)
    if tag:
        qs = qs.filter(tags=tag)
    if search:
        qs = qs.filter(
            Q(title__icontains=search) | Q(content__icontains=search)
        )

    return qs.order_by('-is_pinned', '-priority', '-updated_at')
    # Order: pinned first → priority → recently updated


def get_user_notebooks(user):
    """Notebooks with the number of active notes."""
    return Notebook.objects.filter(user=user).annotate(
        note_count=Count('notes', filter=Q(notes__is_archived=False))
        # annotate: notebook.note_count is available in the template
    ).order_by('-is_default', 'title')


def get_user_tags(user):
    """Tags with the number of notes."""
    return Tag.objects.filter(user=user).annotate(
        note_count=Count('notes')
    ).order_by('name')


def get_note_for_user(user, pk):
    """
    Returns the note if it belongs to user, or None.
    Used in a view instead of get_object_or_404 when softer handling is needed.
    """
    return Note.objects.filter(user=user, pk=pk).select_related('notebook').first()

Step 8 — services.py

hello_app/services.py:

from django.db import transaction

from .models import Note, Notebook, Tag


def create_note(*, user, title, content='', notebook=None, priority=1, tag_ids=None):
    """
    Creates a note with tags atomically.

    transaction.atomic(): either the Note and the tags are saved together, or nothing.
    Tag protection: filter(user=user) → other users' tags are ignored (Mass Assignment protection).
    """
    with transaction.atomic():
        note = Note.objects.create(
            user=user,
            title=title,
            content=content,
            notebook=notebook,
            priority=priority,
        )
        if tag_ids:
            valid_tags = Tag.objects.filter(id__in=tag_ids, user=user)
            # filter(user=user): Protection! A user cannot attach other users' tags.
            note.tags.set(valid_tags)
    return note


def update_note(note, *, title=None, content=None, notebook=..., priority=None,
                is_pinned=None, is_archived=None, tag_ids=None):
    """
    Updates only the passed fields.
    update_fields → UPDATE only the changed columns (more efficient).

    notebook=... (Ellipsis) = "not passed" (notebook is not changed).
    notebook=None           = "remove the notebook" (SET_NULL).
    notebook=<Notebook>     = "set the notebook".
    Ellipsis distinguishes "not passed" from "passed None".
    """
    changed = []
    if title is not None:
        note.title = title
        changed.append('title')
    if content is not None:
        note.content = content
        changed.append('content')
    if notebook is not ...:              # Ellipsis = the "not passed" sentinel
        note.notebook = notebook         # None → SET_NULL; <Notebook> → set it
        changed.append('notebook')
    if priority is not None:
        note.priority = priority
        changed.append('priority')
    if is_pinned is not None:
        note.is_pinned = is_pinned
        changed.append('is_pinned')
    if is_archived is not None:
        note.is_archived = is_archived
        changed.append('is_archived')

    if changed:
        changed.append('updated_at')  # Always update the timestamp
        note.save(update_fields=changed)
        # SQL: UPDATE hello_app_note SET title=..., updated_at=... WHERE id=...
        # NOT: UPDATE hello_app_note SET ... (all fields)

    if tag_ids is not None:
        valid_tags = Tag.objects.filter(id__in=tag_ids, user=note.user)
        note.tags.set(valid_tags)   # DELETE old ones + INSERT new ones

    return note


def toggle_pin_note(note):
    """Toggles is_pinned and saves it to the DB."""
    note.is_pinned = not note.is_pinned
    note.save(update_fields=['is_pinned', 'updated_at'])
    return note


def delete_note(note):
    """
    Deletes a note.
    CASCADE will automatically delete the related Reminders.
    """
    note.delete()


@transaction.atomic
def create_notebook(user, title, is_default=False):
    """
    Business rule: a user has ONLY ONE default notebook.
    Atomically reset the old one and set the new one.
    """
    if is_default:
        Notebook.objects.filter(user=user, is_default=True).update(is_default=False)
        # UPDATE all to default=False before creating the new one
    return Notebook.objects.create(user=user, title=title, is_default=is_default)

Step 9 — Thin views

hello_app/views.py:

from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.shortcuts import get_object_or_404, redirect, render

from .forms import NoteForm
from .models import Note
from . import selectors, services


@login_required   # ← AnonymousUser → redirect to /accounts/login/?next=...
def note_list(request):
    search   = request.GET.get('q', '')

    # The view only parses parameters — the logic is in the selector
    notes     = selectors.get_user_notes(
        request.user,
        archived=False,
        search=search or None,
    )
    tags      = selectors.get_user_tags(request.user)
    notebooks = selectors.get_user_notebooks(request.user)

    return render(request, 'hello_app/note_list.html', {
        'notes':     notes,
        'tags':      tags,
        'notebooks': notebooks,
        'search':    search,
    })


@login_required
def note_create(request):
    if request.method == 'POST':
        form = NoteForm(request.POST, user=request.user)
        if form.is_valid():
            note = services.create_note(
                user     = request.user,
                title    = form.cleaned_data['title'],
                content  = form.cleaned_data.get('content', ''),
                notebook = form.cleaned_data.get('notebook'),
                priority = form.cleaned_data.get('priority', 1),
                tag_ids  = [t.id for t in form.cleaned_data.get('tags', [])],
            )
            messages.success(request, f'Нотатку "{note.title}" створено!')
            return redirect('hello_app:note_detail', pk=note.pk)
    else:
        form = NoteForm(user=request.user)

    return render(request, 'hello_app/note_form.html', {'form': form, 'title': 'Нова нотатка'})


@login_required
def note_edit(request, pk):
    note = get_object_or_404(Note, pk=pk, user=request.user)
    # pk + user=request.user → 404 if it is someone else's note (IDOR protection)

    if request.method == 'POST':
        form = NoteForm(request.POST, instance=note, user=request.user)
        if form.is_valid():
            services.update_note(
                note,
                title    = form.cleaned_data['title'],
                content  = form.cleaned_data.get('content', ''),
                notebook = form.cleaned_data.get('notebook'),
                priority = form.cleaned_data.get('priority'),
                tag_ids  = [t.id for t in form.cleaned_data.get('tags', [])],
            )
            messages.success(request, 'Нотатку оновлено!')
            return redirect('hello_app:note_detail', pk=note.pk)
    else:
        form = NoteForm(instance=note, user=request.user)

    return render(request, 'hello_app/note_form.html', {
        'form': form, 'note': note, 'title': f'Редагувати: {note.title}'
    })


@login_required
def note_delete(request, pk):
    note = get_object_or_404(Note, pk=pk, user=request.user)

    if request.method == 'POST':
        title = note.title
        services.delete_note(note)
        messages.warning(request, f'Нотатку "{title}" видалено.')
        return redirect('hello_app:note_list')

    return render(request, 'hello_app/note_confirm_delete.html', {'note': note})

File structure

hello_app/
├── __init__.py
├── admin.py            ← ModelAdmin registration
├── apps.py
├── context_processors.py ← sidebar_context (if present)
├── forms.py            ← NoteForm, NotebookForm, TagForm
├── models.py           ← UserProfile, Tag, Notebook, Note, Reminder
├── selectors.py        ← Only SELECT operations
├── services.py         ← CREATE/UPDATE/DELETE + transaction.atomic
├── views.py            ← Thin view functions
├── urls.py             ← path() routes
├── migrations/
│   ├── __init__.py
│   ├── 0001_initial.py
│   └── 0002_*.py
└── templates/
    └── hello_app/
        ├── note_list.html
        ├── note_detail.html
        ├── note_form.html
        └── note_confirm_delete.html

Practical exercise

  1. Draw an ER diagram for a "Library" system: Book, Author, Genre, Review. Specify the relationship types and the on_delete for each FK.
  2. Implement the models with proper related_name, __str__, ordering.
  3. Write get_books_by_genre(genre) in selectors.py with select_related('author').prefetch_related('genres').
  4. Write create_book(*, title, author_id, genre_ids) in services.py with transaction.atomic().
  5. Check in the Django shell: change the author of a book and verify that updated_at was updated.

Self-check checklist

  • [ ] I know the difference between OneToOneField, ForeignKey, ManyToManyField
  • [ ] I know where the FK lives (on the "Many" side)
  • [ ] I consciously choose on_delete for each FK (not just CASCADE)
  • [ ] selectors.py contains only SELECTs (filter, annotate, prefetch_related)
  • [ ] services.py uses transaction.atomic() where atomicity is needed
  • [ ] views.py does not contain Model.objects.* directly
  • [ ] I understand N+1 and use select_related/prefetch_related
  • [ ] Q objects — for OR conditions in filter

Next

Next step: 04 — Templates, Crispy Forms and Dashboard — 3-level Template Inheritance, Crispy Forms, Context Processor, Sidebar.

Documentation modules: - Database and ORM - Application Architecture — Services & Selectors - ORM Cheatsheet