Database Design Basics for Web Applications

Good database design for a web application comes down to a few habits: model the real things in your business as tables, give every row a stable primary key, connect related tables with foreign keys, store each fact in one place (normalisation), choose precise data types, add indexes for the queries you actually run, and enforce rules with constraints. These choices decide how fast the application stays and how much trouble future features cause.

The examples use MySQL/MariaDB syntax, but the principles apply to any relational database.

1. Start from the domain

List the things your application deals with and how they relate. For a simple store:

• a customer places many orders;
• an order has many order items;
• each order item refers to one product.

Draw this before writing any code. Each "thing" usually becomes a table; each relationship becomes a foreign key or a joining table.

2. Primary keys

Every table needs a primary key that uniquely identifies a row and never changes:

• auto-increment integers are compact and fast;
• UUIDs or ULIDs are useful when IDs are generated outside the database or must not be guessable.

Never use something that can change (an email address, a phone number) as the primary key.

3. Relationships and foreign keys

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Foreign keys stop orphaned data (an order for a customer that does not exist) and document the relationships. Decide deliberately what happens on delete: block it, cascade, or set to null.

Many-to-many relationships (products and categories) need a joining table with two foreign keys.

4. Normalisation, sensibly

Normalisation means storing each fact once. If a customer's address is copied into every order, a change must be made in many places and copies drift apart.

But some duplication is correct: an order should store the price at the time of purchase, because the product's price will change later. The rule is: normalise current facts, keep historical snapshots where the business needs them.

5. Choose precise data types

• Money: use DECIMAL(12,2) (or store minor units as integers), never floating-point types, which cause rounding errors.
• Dates and times: proper DATE/DATETIME/TIMESTAMP types, with a consistent time-zone policy (often UTC in the database).
• Text: VARCHAR with sensible limits; utf8mb4 character set to support all languages, including Bangla, and emoji.
• Booleans and enums: small types or reference tables, consistently.
• JSON columns: useful for flexible attributes, but not a substitute for proper columns you need to filter or join on.

6. Indexes for real queries

Indexes make lookups fast but cost space and slightly slow writes. Add them for:

• foreign key columns;
• columns used in frequent WHERE, JOIN and ORDER BY clauses;
• combined indexes in the order queries use them, for example (customer_id, created_at) for "this customer's latest orders".

Use EXPLAIN to check queries use the index you expect. Tuning on a live server is covered in MySQL and MariaDB performance tuning.

7. Constraints protect your data

• NOT NULL where a value is required.
• UNIQUE for values that must not repeat (email addresses, order numbers).
• CHECK constraints or application validation for allowed ranges.

Constraints catch bugs that application code misses.

8. Plan for change with migrations

Store schema changes as versioned migrations in your code repository, as frameworks like Laravel do. Every environment (development, staging, production) then has the same structure, and changes are reviewed like code.

For large tables, plan changes that lock tables or rewrite data carefully, and test them on a copy first.

Worked example: a small booking system

A clinic wants online appointment booking. A first design:

CREATE TABLE patients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  created_at TIMESTAMP NOT NULL,
  UNIQUE KEY uq_patients_phone (phone)
);

CREATE TABLE doctors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(150) NOT NULL,
  speciality VARCHAR(100) NOT NULL
);

CREATE TABLE appointments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id BIGINT UNSIGNED NOT NULL,
  doctor_id BIGINT UNSIGNED NOT NULL,
  starts_at DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'booked',
  fee DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(id),
  UNIQUE KEY uq_doctor_slot (doctor_id, starts_at),
  KEY idx_patient_time (patient_id, starts_at)
);

Design choices worth noting:

• the unique key on doctor and start time makes double-booking impossible at the database level, even if two people book at the same moment;
• the fee is stored on the appointment, so later price changes do not alter past records;
• the index on patient and time supports "show this patient's upcoming appointments";
• personal data is limited to what the clinic needs.

Common design mistakes

• Storing several values in one column (comma-separated lists) instead of a related table.
• Using floating-point numbers for money.
• No unique constraints, relying on the application to prevent duplicates.
• Missing indexes on foreign keys.
• Deleting rows that other records depend on, instead of marking them inactive.
• Storing dates as text.

9. Security and privacy in design

• Give the application a database user with only the privileges it needs.
• Store passwords only as hashes, never in plain text.
• Keep personal data to what you need, and plan retention and deletion.
• Use parameterised queries (or an ORM) to prevent SQL injection.

See database security best practices.

Frequently Asked Questions

Should I use an ORM?

An ORM (such as Laravel's Eloquent) speeds up development and protects against many injection mistakes. Learn to check the SQL it generates, to avoid slow query patterns.

Is it bad to store JSON in a relational database?

Not if used for genuinely flexible data. Data you filter, join or report on usually belongs in proper columns.

How do I avoid N+1 query problems?

Load related data in bulk (eager loading) instead of one query per row, and watch query counts during development.

Related reading

See Laravel for business applications and how to choose a web development stack. For a custom application designed for you, see ServerNeed custom web development.

Data design belongs early in the project plan; see how to plan a website project.

Sources

• MySQL Reference Manual: Optimization and Indexes
• MariaDB documentation: Foreign keys