Your database outlives most other technical decisions. Frameworks change and interfaces are redesigned, but the data and its structure stay. Choosing the right kind of database, and designing it well, makes a product fast, reliable and easy to extend. This guide explains the fundamentals in practical terms.

Relational (SQL) databases

Relational databases such as PostgreSQL, MySQL and SQL Server store data in tables of rows and columns, linked by keys.

Core ideas

  • Tables for each kind of thing: customers, orders, products.
  • Primary keys uniquely identify each row.
  • Foreign keys link rows, such as an order to its customer, and the database enforces that the link is valid.
  • Constraints keep data correct: required fields, unique email addresses, allowed values.
  • Transactions group changes so they all succeed or all fail. Moving money between accounts never leaves one side updated and the other not. This is what ACID (atomicity, consistency, isolation, durability) guarantees.
  • SQL lets you query and combine data flexibly, including reports nobody planned for.

Normalisation

Normalisation means storing each fact once. A customer's address lives in the customer record, not copied into every order. In practice:

  • Each column holds a single value.
  • Each table describes one kind of thing.
  • Non-key columns depend only on the key.

Normalised data is easy to keep correct. Sometimes you deliberately denormalise, for example storing an order's total or the price at the time of purchase, for speed or for historical accuracy.

NoSQL databases

"NoSQL" covers several different kinds of database, each built for particular access patterns.

  • Document databases (MongoDB, Firestore) store JSON-like documents. Flexible structure and natural for data read as a whole, such as a product with its variants.
  • Key-value stores (Redis, DynamoDB in its simplest use) fetch a value by key extremely quickly. Ideal for caching, sessions and counters.
  • Wide-column stores (Cassandra, ScyllaDB) handle huge write volumes spread across many servers, such as event logs and sensor data.
  • Graph databases (Neo4j) model relationships directly, which suits social networks, recommendations and fraud detection.

How to choose

Choose a relational database when:

  • Data is naturally connected: customers, orders, invoices, inventory.
  • Correctness matters, such as money, stock and bookings.
  • You need flexible reporting and ad-hoc queries.

Choose a document database when:

  • Records vary in structure or change shape often.
  • Data is mostly read and written as whole documents.
  • You want fast iteration early in a product's life.

Add a key-value store when you need caching, sessions, rate limiting or queues alongside your main database.

In practice, most products need a primary database plus Redis for caching. PostgreSQL deserves special mention: with its JSONB type it stores flexible documents alongside relational tables, which covers many cases people reach for NoSQL to solve. And MongoDB supports multi-document transactions, so the line between the two families is less sharp than it used to be.

Design for how data is used

Whichever type you choose, start from the questions the product will ask:

  • Which screens load which data?
  • Which queries run most often, and which must be fastest?
  • What will the largest tables or collections look like in two years?

In relational databases, design a clean normalised model and add indexes for the important queries. In document databases, design documents around access patterns: embed data that is always read together, and reference data that is shared or grows without limit, such as comments on a popular post.

Indexing

Indexes make reads fast and writes slightly slower.

  • Index columns used in filters, joins and sorting.
  • Use compound indexes that match common query patterns, in the right column order.
  • Check query plans (EXPLAIN in SQL, explain() in MongoDB) for slow queries.
  • Remove indexes that are never used.

Scaling

  1. Optimise first. Better queries and indexes often fix "scaling" problems.
  2. Scale vertically. A larger database server is the simplest next step.
  3. Add caching for frequently read data.
  4. Add read replicas for reporting and read-heavy traffic.
  5. Partition very large tables, such as events by month.
  6. Shard across several servers only when necessary; it adds real complexity.

Backups and recovery

  • Automate daily backups and point-in-time recovery where available.
  • Store backups in a different location or account.
  • Test restores regularly; an untested backup is not a backup.
  • Know how much data you can afford to lose and how long you can afford to be down, and design to match.

Security

  • Never expose the database directly to the internet.
  • Use separate users with the least privilege each service needs.
  • Encrypt data at rest and in transit.
  • Use parameterised queries to prevent injection attacks.
  • Store passwords only as strong hashes, never in plain text.
  • Collect and keep only the personal data you actually need.

Common mistakes

  • Choosing a database because it is fashionable rather than because it fits the data.
  • Storing money as floating-point numbers instead of integers or decimals.
  • No indexes until the product is slow, or indexes on everything.
  • Copying the same data into many places without a plan to keep it in sync.
  • Ignoring time zones; store timestamps in UTC.
  • No migration process for schema changes.

A quick decision checklist

  • Is the data highly connected? Lean relational.
  • Does it vary in shape and get read as whole records? Consider documents.
  • Do you need strict consistency for money or stock? Use transactions, usually relational.
  • Do you need very fast lookups or caching? Add Redis.
  • Does your team already know one option well? That counts for a lot.

Conclusion

Good database design starts with how the product uses its data, chooses the right type of store for each job, and adds indexes, backups and security from the start. If you are planning a platform and want a second opinion on its data model, book a discovery call.