Designing the data model

From stories to data

Read your user stories and underline the nouns: student, note, title, tag. These nouns are clues to what the app must store.

An entity is one kind of thing you store, such as a user or a note. An attribute is a detail of that thing, such as a note's title or a user's email. In a database, each entity becomes a table and each attribute becomes a column.

Keys: how rows find each other

A primary key is a column whose value is unique for every row, usually a number called id. A foreign key is a column that holds the primary key of a row in another table. In notes, the column user_id is a foreign key: it says which user owns the note.

Relationships

• One-to-many: one user has many notes, but each note belongs to one user. Put user_id in the notes table.
• Many-to-many: one note can have many tags, and one tag can be on many notes. You need a third table, note_tags, with one row per link (note_id, tag_id).
• One-to-one: one user has one profile. Rare; often the columns just go in the same table.

The table above is our ER diagram written as text. ER means Entity-Relationship: a drawing of the entities and the lines between them. On paper you would draw a box for each table and a line from notes.user_id to users.id, with "1" at the users end and "many" at the notes end.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TEXT
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  body TEXT,
  pinned INTEGER DEFAULT 0,
  created_at TEXT,
  updated_at TEXT
);

Choosing types and timestamps

Pick the simplest type that fits. Text goes in TEXT. Whole numbers and ids go in INTEGER. SQLite has no true/false type, so pinned is stored as INTEGER: 1 for true, 0 for false. Timestamps (the date and time something happened) are stored as text in a fixed format such as 2026-02-01 08:00, so they sort correctly.

Add created_at and updated_at to almost every table. They cost little and help you sort ("newest first"), debug ("when did this change?") and answer users' questions.