A scalable database starts with the data relationships and queries your application actually needs. Normalization can reduce accidental duplication; indexes can speed selected lookups. Neither guarantees fast queries at every scale, and partitioning adds complexity that should be justified by measurements.
Model identity separately from display text
For a hypothetical order system, customer names are editable display data. Use a stable customer identifier to connect records. Decide whether an order needs a snapshot of the delivery address at purchase time: reading today's customer address may give the wrong historical answer.
A complete SQLite example
The following runs against a fresh SQLite database. It enables foreign keys explicitly, creates distinct tables and inserts a valid relationship. It does not represent a complete ordering system.
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
status TEXT NOT NULL CHECK (status IN ('pending', 'paid'))
);
CREATE INDEX orders_customer_status ON orders(customer_id, status);
INSERT INTO customers(id, name) VALUES (1, 'Example customer');
INSERT INTO orders(id, customer_id, status) VALUES (100, 1, 'pending');
SELECT id FROM orders WHERE customer_id = 1 AND status = 'pending';The query returns order 100. SQLite foreign keys documents the need to enable foreign-key enforcement per connection in ordinary SQLite configurations. A production application must also handle transactions, authorization and migrations.
Measure the query before adding infrastructure
| Observation | First check |
|---|---|
| Slow customer lookup | Query predicates and a suitable index |
| Large result transfer | Pagination and selected columns |
| Duplicate or orphan records | Constraints and write transactions |
| Write contention | Transaction duration and concurrency |
PostgreSQL EXPLAIN explains PostgreSQL query plans. EXPLAIN ANALYZE executes the statement, so understand its effects before using it. Do not infer PostgreSQL behavior from a SQLite demonstration or add partitions because the table merely looks large.
Decide what to optimize next
Collect representative timings and workload data, including writes. Every additional index has maintenance cost. Preserve a repeatable baseline and verify the business result after changing a query. For custom software scope the important reads, writes and integrity rules together.
Example: design a service request queue around its queries
Practical example updated .
For an illustrative staff portal, start with the questions people need answered: which requests are unassigned, which are waiting on the customer, and what changed since yesterday? A proposed data model should make those questions explicit before a database or hosting tier is selected.
- Separate the records. Keep customers, requests, assignments, and status changes distinct. Link them with stable identifiers; a customer’s display name is not an identifier.
- Specify the history. Store the actor, time, and transition when a request changes status. Decide which facts need a historical snapshot and which should show the customer’s current information.
- Design for concurrent edits. Define what happens when two staff members assign the same request. The application must detect or resolve that conflict instead of silently losing an update.
- Measure the actual queue. Test the filter and sort used by staff, with representative record counts and distributions. Inspect the query plan before adding an index; an index for a customer lookup may not serve an unassigned-request queue.
Acceptance checks before adding more infrastructure
- A staff role cannot read or change requests outside its permitted scope; test the server response, not only hidden UI controls.
- A failed status change leaves the request and its history consistent.
- Duplicate input and simultaneous edits have defined, tested outcomes.
- Queue pages stay within an agreed response-time target on representative data; record the dataset and query used.
The SQLite example above demonstrates a small relationship and lookup. It does not establish a production capacity limit, a complete permission model, or the right database for every application. Those decisions need the workload, recovery requirements, and measurements from your own system.
SQLite: how query plans and indexes affect searching and sorting
Connect the data model to the people using it
Before choosing database features, map the records, editing permissions, and tasks the first release must support. Apolon Plovila is a delivered example of product discovery, customer enquiries, and private content administration. It illustrates the application workflow, not a published database benchmark or proof of a particular database architecture.