KAHIBARO
Discord Login Register ☰
☰

9.10 Database Schema Design

Table of Contents

Why Schema Design Matters

When you build any non‑trivial backend, your database schema quickly becomes one of the most important parts of your system. A good schema:

A bad schema:

In this chapter we focus on how to think about designing relational database schemas. We will not go deep into SQL syntax or normalization theory proofs, because those have their own chapters. Here we look at the practical process and patterns.

From Requirements to Tables

Schema design always starts from the data and the questions you need to answer.

Step 1: Identify entities

An entity is a thing you need to store information about.

Example: Simple blog application

You might see entities like:

Each entity usually becomes a table.

Example mapping:

EntityLikely table name
Userusers
Postposts
Commentcomments
Tagtags

Try a similar exercise for an online bookstore:

Already you can imagine tables like books, authors, customers, orders, order_items, payments.

Step 2: Identify attributes

Attributes are the properties of each entity. These become columns.

Example: users table

Example: posts table

Do not worry too much about exact data types at this stage, only about what must be stored.

Step 3: Identify relationships

Entities rarely live alone. You need to understand how they relate.

Typical questions:

Examples:

You already saw separate chapters for relationships, so here we only talk about the impact on schema design:

Example design:

text 
users
  id (PK)
  email
  ...
posts
  id (PK)
  author_id (FK -> users.id)
  ...
comments
  id (PK)
  post_id (FK -> posts.id)
  author_id (FK -> users.id)
  ...
tags
  id (PK)
  name
post_tags
  post_id (FK -> posts.id)
  tag_id (FK -> tags.id)
  PRIMARY KEY (post_id, tag_id)

The post_tags table exists only to represent the many‑to‑many relationship.

Choosing Table and Column Names

Names are part of the design. Clear names reduce confusion and bugs.

Naming tables

Common conventions:

Examples:

GoodAvoid
usersUserTable
order_itemsOrderItemsTable
blog_postsBP or blogPosts

Tips:

Naming columns

Keep columns simple and descriptive.

Examples:

GoodAvoid
iduser_id_number_123
created_atcreationDate
updated_atlastUpdateTimeStamp
is_activeactive_or_not
emailuserEmailAddress

Common patterns:

Example table:

text 
orders
  id
  customer_id
  total_amount
  currency
  status
  created_at
  updated_at

Primary Keys in Schema Design

You already know what primary keys are. In schema design you must decide how to define them.

Natural vs surrogate keys

Example natural keys:

Example surrogate keys:

Why surrogate keys are common

Surrogate keys are often preferred for main primary keys because:

Typical pattern:

text 
users
  id SERIAL PRIMARY KEY
  email TEXT UNIQUE NOT NULL
  ...
orders
  id UUID PRIMARY KEY
  user_id INTEGER REFERENCES users(id)
  ...

Email is unique, but id is still the primary key.

Composite primary keys

A composite primary key uses several columns together as the primary key.

You usually see this in join tables.

Example:

text 
post_tags
  post_id INTEGER REFERENCES posts(id)
  tag_id INTEGER REFERENCES tags(id)
  PRIMARY KEY (post_id, tag_id)

Here, (post_id, tag_id) uniquely identifies a row. There is no separate id column.

Another example: an order item

text 
order_items
  order_id INTEGER REFERENCES orders(id)
  line_number INTEGER
  product_id INTEGER REFERENCES products(id)
  quantity INTEGER NOT NULL
  PRIMARY KEY (order_id, line_number)

Each order has line numbers 1, 2, 3 and so on. The pair (order_id, line_number) is unique.

Important rule: If an entity can be naturally identified by a small combination of columns, a composite primary key is often a good fit, especially in relationship tables.

Foreign Keys and Relationships in Design

Foreign keys connect your tables.

One‑to‑many example: Users and Posts

Design:

text 
users
  id (PK)
  email
  ...
posts
  id (PK)
  author_id (FK -> users.id)
  title
  content

author_id is a foreign key. This enforces that every post references an existing user.

You can decide what happens when a user is deleted:

You choose this behavior based on business rules.

Example SQL idea (syntax simplified):

sql 
author_id INTEGER REFERENCES users(id) ON DELETE CASCADE

Many‑to‑many example: Products and Categories

Design:

text 
products
  id (PK)
  name
  price
categories
  id (PK)
  name
product_categories
  product_id (FK -> products.id)
  category_id (FK -> categories.id)
  PRIMARY KEY (product_id, category_id)

To find all products in a category, you join products with product_categories.

To find all categories of a product, you join categories with product_categories.

Required vs Optional Data

Not every column must always have a value.

NOT NULL vs NULL

You decide which columns are required and which are optional.

Example:

text 
users
  id (PK)
  email NOT NULL
  password_hash NOT NULL
  full_name NOT NULL
  bio NULL
  avatar_url NULL
  created_at NOT NULL

Rule: Use NOT NULL for every column that must always have a value. Use NULL only when "no value" is a valid and expected state.

Examples of clearly required fields:

Examples of clearly optional fields:

Default values

Default values simplify inserts and make data more consistent.

Examples:

text 
users
  is_active BOOLEAN NOT NULL DEFAULT TRUE
  created_at TIMESTAMP NOT NULL DEFAULT now()
orders
  status TEXT NOT NULL DEFAULT 'pending'
  currency TEXT NOT NULL DEFAULT 'USD'

If you insert a user without specifying is_active, it becomes TRUE.

Use defaults where most rows will have the same value.

Avoiding Redundant and Inconsistent Data

Poor schema design often leads to duplicated data that becomes inconsistent.

Obvious duplication example

Bad design:

text 
orders
  id
  customer_id
  customer_email
  customer_name

Here the email and name are copied from customers. If the customer updates their email, which value is correct?

Better design:

text 
customers
  id
  email
  name
  ...
orders
  id
  customer_id (FK -> customers.id)
  ...

When you need the email for an order, you join orders with customers.

Legitimate duplication example

Sometimes you need to store historical data at the time of an event.

Example: storing the price of a product when the order is placed.

Bad idea:

text 
order_items
  id
  order_id
  product_id
  -- later you select product price from products table

If product price changes over time, past orders become inaccurate.

Better design:

text 
order_items
  id
  order_id
  product_id
  unit_price_at_order
  quantity

You duplicate the price value intentionally, because it represents "price at order time", which should not change.

Key idea:

Denormalization for Performance

Normalization is about reducing redundancy and improving consistency. However, sometimes a fully normalized design becomes too slow or too complex for common queries.

Denormalization means you intentionally duplicate or precompute some data to speed up reads.

You will see normalization concepts separately, so here we only look at practical cases.

Example: storing counts

Normalized design:

text 
posts
  id
  title
  content
comments
  id
  post_id
  content

To get the number of comments for a post, you count on the comments table:

sql 
SELECT COUNT(*) FROM comments WHERE post_id = :post_id;

For a small app this is fine. For a big app with millions of comments, this may be slow if you do it all the time.

Denormalized design:

text 
posts
  id
  title
  content
  comments_count INTEGER NOT NULL DEFAULT 0
comments
  id
  post_id
  content

When you insert a comment, you also increment posts.comments_count. Now you can show the count directly from posts without counting every time.

Trade‑off: You must keep comments_count accurate in your application logic or with database triggers.

Example: caching computed values

Imagine a users table and orders table. You want to often display each user's "total_spent".

Normalized:

Denormalized:

text 
users
  id
  email
  total_spent NUMERIC NOT NULL DEFAULT 0

On each new paid order, you update users.total_spent. Reads become trivial.

Rule: Start with a normalized schema. Only denormalize when you have measured performance problems and you understand the consistency cost.

Modeling Common Patterns

Let us look at a few recurring schema patterns and how to model them.

Soft deletes

Sometimes you do not want to delete rows physically. You want to "soft delete" them so you can restore later or keep history.

Common options:

  1. is_deleted boolean flag:
text 
users
  id
  email
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE

Application queries must filter out deleted records: WHERE is_deleted = FALSE.

  1. deleted_at timestamp:
text 
users
  id
  email
  deleted_at TIMESTAMP NULL

If deleted_at is NULL, user is active. If not NULL, user is deleted.

Advantage: you know when deletion occurred.

Status fields

Many entities go through states, such as orders or tickets.

Example: order status

text 
orders
  id
  customer_id
  status -- 'pending', 'paid', 'shipped', 'cancelled'
  placed_at
  paid_at
  shipped_at
  cancelled_at

Use clear status values. You can enforce allowed values with constraints or enums in the database.

You often store both the status and the timestamps of important transitions.

Polymorphic relationships

Sometimes an entity can belong to more than one type of parent.

Example: You want to store "likes", which can apply to posts or comments.

Option 1: separate tables

text 
post_likes
  user_id
  post_id
comment_likes
  user_id
  comment_id

Option 2: polymorphic relation

text 
likes
  id
  user_id
  target_type -- 'post' or 'comment'
  target_id

With option 2 you must check in application logic that target_id actually exists in the right table.

As a beginner, prefer separate tables when possible. They are simpler and enforce referential integrity.

Designing for Queries

You design a schema not only from entities, but also from the questions you need to answer.

When planning tables, always ask:

Example: simple task manager

Requirements:

Draft schema:

text 
tasks
  id
  user_id
  title
  description
  status -- 'open', 'done'
  created_at
  due_at

Now think queries:

This influences your indexing decisions, which you will learn later. For now, the important part is to design columns that support the queries.

Bad design example:

text 
tasks
  id
  user_id
  title
  description
  is_done
  is_archived
  is_pinned
  status_text

You have multiple columns representing the same concept (status). Better to use a single status column.

Better design:

text 
tasks
  id
  user_id
  title
  description
  status -- 'open', 'done', 'archived', 'pinned'
  created_at

Or, if "pinned" is independent of status:

text 
tasks
  id
  user_id
  title
  description
  status -- 'open', 'done', 'archived'
  is_pinned BOOLEAN NOT NULL DEFAULT FALSE
  created_at

Example: Step‑by‑Step Design for a Simple E‑Commerce

Let us walk through a small example to see the process.

Requirements

Step 1: Entities

Step 2: Attributes

Customer:

Product:

Order:

OrderItem:

Step 3: Relationships

Schema sketch:

text 
customers
  id (PK)
  email UNIQUE NOT NULL
  password_hash NOT NULL
  full_name NOT NULL
  created_at NOT NULL
products
  id (PK)
  name NOT NULL
  description
  price NUMERIC NOT NULL
  currency TEXT NOT NULL DEFAULT 'USD'
  is_active BOOLEAN NOT NULL DEFAULT TRUE
orders
  id (PK)
  customer_id (FK -> customers.id) NOT NULL
  status TEXT NOT NULL DEFAULT 'pending' -- 'pending', 'paid', 'cancelled'
  created_at NOT NULL
  paid_at
  cancelled_at
order_items
  id (PK)
  order_id (FK -> orders.id) NOT NULL
  product_id (FK -> products.id) NOT NULL
  quantity INTEGER NOT NULL
  unit_price NUMERIC NOT NULL
  currency TEXT NOT NULL

Notice:

You can then refine with indexes and constraints later.

Practical Tips and Checklist

When you design a schema, go through a checklist.

Table by table, ask:

  1. Entities
    • Does this table represent one clear concept?
    • Is the table name clear and consistent?
  2. Primary key
    • Is there a primary key?
    • Do I need a surrogate key or is a composite key better?
  3. Columns
    • Are required columns marked NOT NULL?
    • Do optional columns really need to be optional?
    • Are default values set where useful?
  4. Relationships
    • Are foreign keys defined where needed?
    • Is the relationship one‑to‑many, many‑to‑many, or one‑to‑one?
    • Do I need a join table?
  5. Redundancy
    • Am I duplicating data that should be central?
    • If I duplicate data, is it intentionally a historical snapshot?
  6. Usage
    • What are the most common queries on this table?
    • Do the columns support those queries naturally?
  7. Future changes
    • Can I add fields later without breaking existing ones?
    • Am I avoiding hard coding assumptions that are likely to change, such as "there are only three possible statuses forever"?

If you walk through this process and discuss the schema with teammates or just yourself on paper, you will already avoid many common problems.

This is the practical side of database schema design: start from the real world, map it to tables, think about relationships and queries, avoid unnecessary duplication, and only optimize with denormalization when you have to.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3176 / 24h
Impressum / Imprint
Datenschutz / Data Policy