ER diagrams and relationship types¶
Three types of relationships between tables. The question "where does the FK live?" is not a technical one, it is an architectural one.
Relationship types¶
1:1 — OneToOneField¶
FK + UNIQUE · "user has exactly one profile"
USER USER_PROFILE
┌──────────────┬──────────┐ ┌────────────────┬──────────────────┐
│ id bigint PK│ │──►│ id bigint PK│ │
│ username │ │ │ user_id bigint │ UNIQUE FK │
└──────────────┴──────────┘ │ display varchar│ │
│ timezone │ │
└────────────────┴──────────────────┘
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'
Why is this 1:1? An ordinary
FK+UNIQUEon the column. The reverse is an attribute (not a QuerySet):user.profile. Useful for vertical partitioning: 5 basic fields in User, 20 optional ones in Profile — we do not clutter the main table.
When to use 1:1?
- "Vertical partitioning": 5 basic fields in User, 20 optional ones in Profile
- Different access patterns: User changes often, Profile rarely → a separate QuerySet
- Different permissions: view_user without view_userprofile
1:N — ForeignKey¶
FK on the "Many" side · "notebook has many notes"
NOTEBOOK NOTE NOTE NOTE
┌──────────┬──────────┐ ┌───────────────────────┐
│ id │ bigint PK│──┬─►│ id bigint PK │
│ title │ varchar │ │ │ notebook_id bigint FK │← the FK is here!
└──────────┴──────────┘ ├─►│ title varchar │
│ └───────────────────────┘
├─► (another note)
└─► (another note)
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
)
Where does the FK live? Always on the Many side.
Notebookdoes not store a list of notes —Notestoresnotebook_id. This is the physics of SQL: one column = one value.
WRONG way of thinking: CORRECT way of thinking:
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 break the 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) # Filter
notebook.notes.count() # Count
note = Note.objects.get(id=1)
note.notebook # ← FK attr → a Notebook object (or None)
note.notebook_id # ← the raw integer FK without a JOIN
note.notebook.title # ← JOIN: SELECT ... WHERE notebook_id=...
M:N — ManyToManyField¶
Via a junction table · Django creates it automatically
NOTE NOTE_TAGS (junction) TAG
┌──────────┬──────┐ ┌──────────┬──────────┐ ┌──────────┬──────────┐
│ id │ PK │──►│ note_id │ bigint FK│ │ id │ PK │
│ title │ │ │ tag_id │ bigint FK│─►│ name │ varchar │
└──────────┴──────┘ └──────────┴──────────┘ └──────────┴──────────┘
UNIQUE(note_id, tag_id)
Django: hello_app_note_tags
class Note(models.Model):
tags = models.ManyToManyField(
'Tag',
blank=True, # A note can have no tags
related_name='notes', # tag.notes.all() → notes with this tag
)
# Django automatically creates the junction table: hello_app_note_tags
M:N operations:
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 the old ones + INSERT the new ones
# Clear them 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=...
A junction = a separate table. There is no magic here — it is an ordinary table with two FKs. Want to add
added_atorroleto the relationship? Passthrough=and describe your own junction model.
on_delete — what happens when the parent is deleted?¶
Mistake: CASCADE as the default for everything → deleting one user can wipe out hundreds of rows. Always choose the strategy deliberately. It is a business decision, not a technical one.
# 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 stays
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
# (a hypothetical example: in our project Note.tags is M:N, and M:N has no on_delete)
class Note(models.Model):
category = models.ForeignKey(Category, on_delete=models.PROTECT)
# Deleting a Category → ProtectedError (an exception)
# Logic: do not allow a category to be deleted 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 non-existent id)
# AVOID: leads to IntegrityError or dirty data
The on_delete selection matrix¶
| Strategy | What it does | When to choose it |
|---|---|---|
CASCADE |
Delete the children automatically | Reminder → Note: a reminder without a note makes no sense |
SET_NULL |
FK → NULL, the children live on | Note → Notebook: a note exists without a notebook. The field must be null=True |
SET_DEFAULT |
FK → the default value |
Note → Notebook: move it to the "Default" notebook |
PROTECT |
ProtectedError, deletion is forbidden |
Category (hypothetical): do not allow deleting a category while it is in use |
RESTRICT |
Forbid, but allow a cascade from a higher level | Complex domains with several levels of ownership |
DO_NOTHING |
The DB decides by itself (often = orphan) | Almost never. Only if you manage consistency manually (triggers) |
Specifically for the Notes Platform:
| If you delete... | What do we want with the children? | on_delete |
|---|---|---|
| Note | Delete the Reminders (they make no sense without the Note) | CASCADE |
| Notebook | Keep the Notes (the note is more important than the notebook) | SET_NULL |
| User | Delete all Notes (the content belongs to the user) | CASCADE |
| Tag | Note.tags is M:N: only the rows in note_tags are deleted, the notes stay |
— (M:N has no on_delete) |
The full schema: 11 tables of the Notes Platform¶
schema_v1 · 11 tables · 1 junction table · postgres-ready
─────────────────────────────────────────────────────────
USER (auth_user) USER_PROFILE ←── 1:1 OneToOne
┌──────────────┬─────────────┐ ┌────────────────┬──────────────────┐
│ id │ bigint PK │──┐ │ id │ bigint PK │
│ username │ varchar(150)│ └──►│ user_id │ bigint UNIQUE FK │
│ email │ varchar(254)│ │ display_name │ varchar(60) │
│ password │ varchar(128)│ │ timezone │ varchar(40) │
│ date_joined │ timestamp │ │ avatar_url │ text │
└──────────────┴─────────────┘ └────────────────┴──────────────────┘
│ owns 1:N │ writes 1:N │ owns tags 1:N
▼ ▼ ▼
NOTEBOOK NOTE (central) TAG
┌─────────────┬──────────┐ ┌───────────────┬──────────────────┐ ┌──────────┬───────────────────┐
│ id │ bigint PK│ │ id │ bigint PK │ │ id │ bigint PK │
│ user_id │ bigint FK│ │ user_id │ bigint FK │ │ user_id │ bigint FK │
│ title │ varchar │ │ notebook_id │ bigint NULL FK │ │ name │ varchar(50) │
│ color │ varchar │ │ title │ varchar(200) │ │ color │ varchar(7) │
│ is_default │ boolean │ │ content │ text │ └──────────┴───────────────────┘
└─────────────┴──────────┘ │ priority │ smallint │ unique_together(user_id, name)
│ contains 1:N │ is_pinned │ boolean │
└─────────────────────►│ is_archived │ boolean │◄──── M:N via NOTE_TAGS
│ created_at │ timestamp │
│ updated_at │ timestamp │
│ views_count │ integer │
└───────────────┴──────────────────┘
│ reminds 1:N
▼
REMINDER NOTE_TAGS (junction)
┌────────────────┬──────────────┐ ┌──────────┬────────────┐
│ id │ bigint PK │ │ note_id │ bigint FK │
│ note_id │ bigint FK │ │ tag_id │ bigint FK │
│ remind_at │ timestamp │ └──────────┴────────────┘
│ is_sent │ boolean │ UNIQUE(note_id, tag_id)
│ repeat_pattern │ varchar(20) │ Django creates it automatically
└────────────────┴──────────────┘
TODO_LIST TODO_ITEM
┌─────────────┬──────────┐ ┌────────────────┬──────────────┐
│ id │ bigint PK│ │ id │ bigint PK │
│ user_id │ bigint FK│───►│ todo_list_id │ bigint FK │
│ title │ varchar │ │ text │ varchar(500) │
│ is_completed│ boolean │ │ is_done │ boolean │
└─────────────┴──────────┘ │ order_position │ integer │
│ due_date │ date NULL │
└────────────────┴──────────────┘
SHOPPING_LIST SHOP_ITEM
┌─────────────┬──────────┐ ┌──────────────────┬──────────────────┐
│ id │ bigint PK│ │ id │ bigint PK │
│ user_id │ bigint FK│───►│ shopping_list_id │ bigint FK │
│ title │ varchar │ │ name │ varchar(120) │
│ store_name │ varchar │ │ quantity │ decimal(8,2) │
└─────────────┴──────────┘ │ unit │ varchar(5) │
│ is_purchased │ boolean │
│ estimated_price │ decimal(10,2) │
└──────────────────┴──────────────────┘
⚠️ DecimalField for money — not FloatField!
Mermaid ER diagram¶
erDiagram
USER ||--o| USER_PROFILE : "1:1 has profile"
USER ||--o{ NOTEBOOK : "1:N owns"
USER ||--o{ NOTE : "1:N writes"
USER ||--o{ TAG : "1:N owns tags"
USER ||--o{ TODO_LIST : "1:N owns"
USER ||--o{ SHOPPING_LIST : "1:N owns"
NOTEBOOK ||--o{ NOTE : "1:N contains (SET_NULL)"
NOTE }o--o{ TAG : "M:N tagged with"
NOTE ||--o{ REMINDER : "1:N reminds (CASCADE)"
TODO_LIST ||--o{ TODO_ITEM : "1:N has items"
SHOPPING_LIST ||--o{ SHOP_ITEM : "1:N has items"
USER {
bigint id PK
string username
string email
}
NOTE {
bigint id PK
bigint user_id FK
bigint notebook_id FK
string title
text content
int priority
boolean is_pinned
timestamp updated_at
}
NOTEBOOK {
bigint id PK
bigint user_id FK
string title
string color
boolean is_default
}
TAG {
bigint id PK
bigint user_id FK
string name
string color
}
REMINDER {
bigint id PK
bigint note_id FK
timestamp remind_at
boolean is_sent
}
TODO_LIST {
bigint id PK
bigint user_id FK
string title
}
TODO_ITEM {
bigint id PK
bigint todo_list_id FK
string text
boolean is_done
int order_position
}
SHOPPING_LIST {
bigint id PK
bigint user_id FK
string title
}
SHOP_ITEM {
bigint id PK
bigint shopping_list_id FK
string name
decimal quantity
boolean is_purchased
decimal estimated_price
}
Flowchart: the model design process¶
flowchart TD
A[Describe the domain in words] --> B["Find the entities<br>nouns in the text"]
B --> C["Define the attributes<br>of each entity"]
C --> D{"Relationship between<br>entities?"}
D -->|"1:1"| E["OneToOneField<br>FK + UNIQUE"]
D -->|"1:N"| F["ForeignKey<br>on the Many side"]
D -->|"M:N"| G["ManyToManyField<br>Django junction table"]
E --> H[Choose on_delete]
F --> H
G --> I["Django will create<br>the junction table"]
H --> J["Normalization:<br>remove duplication"]
J --> K[Write the Django model]
classDef step fill:#eceff1,stroke:#546e7a,stroke-width:1px;
classDef decision fill:#e3f2fd,stroke:#1565c0,stroke-width:2px;
classDef success fill:#e8f5e9,stroke:#2e7d32,stroke-width:2px;
classDef warning fill:#fff8e1,stroke:#e65100,stroke-width:2px;
class A,B,C,E,F,G,H,I step;
class D decision;
class K success;
class J warning;
Navigation¶
- Previous: Domain design
- Next: Django models