Designing a simple database

Where to start

Not with tables. First write down what your project is about: which entities exist and what happens between them.

For a notes service: there are users, there are notes, notes have categories. Three entities, three tables.

The rule is simple: an entity is something you'd call "one of these". One user, one note, one order.

The order of work

1. List the entities. The nouns from your project description.

2. List each one's attributes. For a note: title, body, creation date. Only what's needed now, without provisioning for the future.

3. Determine the relationships. For every pair, ask: how many to how many.

• One to many. A user has many notes; a note has one author. The most common case.
• Many to many. A note has many tags; a tag has many notes. Needs a separate linking table.
• One to one. Rare, and usually a sign the entity shouldn't have been split.

4. Assign keys. Every table gets its own identifier. A one-to-many relationship is expressed by storing the user's identifier on the note.

5. Decide what's required. Is a note without a title allowed? Without an author? Better settled now than repaired later.

Rules that save you

One value per cell. A comma-separated list of tags in one field is the classic beginner's mistake. Such a field can't be searched or updated properly afterwards.

Don't duplicate data. If a user's name lives both in the users table and on every note, renaming them leaves you out of sync.

Creation and modification timestamps. Add them almost always: they help during investigations, and adding them later is harder.

Mark rather than delete. It's often better to flag a record as deleted than to erase it — especially in a learning project where it's easy to remove something you needed.

Mistakes visible from outside

• A forty-column table. Usually means two or three entities got mixed together.
• Numbered fields: phone1, phone2, phone3. That needs its own table.
• Storing what can be computed. If an order total can be derived from its items, it usually shouldn't be stored separately. The exception is when it's genuinely required, such as freezing the price at purchase time.
• No constraints. The database should itself refuse to create a note with a non-existent author.

Checking yourself

Describe five scenarios in words: add, list, filter, edit, delete. For each, check the schema holds enough data and the query doesn't turn into a monster.

If a simple screen requires joining five tables, the schema wants simplifying.

Don't over-engineer early

A learning project doesn't need ten tables for theoretical purity. Three or four tables, clear relationships and sensible constraints are enough — and they read as competent.