Databases: organize information for reliable use

Learn how to model data, ask useful questions with queries, protect consistency with transactions, and choose indexes and database types that fit an application.

What a database is for

A database is an organized collection of data. A database management system (DBMS) is software that stores, retrieves, updates, and protects that data. Applications use databases for accounts, catalogs, messages, transactions, measurements, and many other kinds of information.

Saving values in a file can work for a small task. As the data grows or multiple users need it, an application also needs ways to find records, avoid conflicting updates, enforce rules, recover after a failure, and control access. A DBMS provides tools for these requirements.

• Persistence: data can outlive a program run.
• Queries: request selected information without manually scanning every file.
• Consistency: enforce constraints and coordinate changes.
• Concurrency: allow multiple tasks to use shared data safely.
• Administration: manage access, backups, recovery, and performance.

Relational databases: tables and relationships

A relational database represents data in tables. A table has columns with defined meanings and rows representing records. A relation connects records through values that refer to keys in another table.

Students
student_id	name	email
101	Ari	ari@example.test
102	Sam	sam@example.test

Enrollments
student_id	course_id	status
101	CS1	active
101	CS2	completed
102	CS1	active

The enrollment table links students to courses. Instead of copying a student's name into every enrollment row, it stores the student identifier and joins to the student record when the name is needed. This reduces repeated facts and helps changes stay consistent.

Model data with keys and constraints

A primary key uniquely identifies a row. A foreign key refers to a key in another table and can prevent references to records that do not exist. A unique constraint can ensure that values such as usernames are not duplicated; a not-null constraint requires a value.

A database schema describes tables, columns, data types, relationships, and constraints. A good schema reflects the facts the application needs to preserve. Before creating tables, identify the entities, their attributes, and how they relate.

Normalization and avoiding update anomalies

Normalization is a family of principles for organizing relational data to reduce undesirable duplication. If a student's email is copied into every enrollment row, changing the email requires updating many rows. Missing one creates inconsistent copies; deleting the last enrollment could accidentally delete the only copy of the student's details.

Separating students, courses, and enrollments gives each fact a natural home. Normalization is a useful default, though systems sometimes deliberately duplicate or precompute data for performance. Such denormalization adds responsibility: updates must keep copies in sync.

Null is not the same as zero

A NULL value commonly means that information is missing, unknown, or not applicable. It is not equal to zero or an empty string, and ordinary equality comparisons with NULL do not behave like comparisons with a known value. Queries generally use predicates such as IS NULL.

Query and update data with SQL

SQL (Structured Query Language) is widely used to define, query, and update relational databases. A query describes the desired result; the database engine chooses a plan to retrieve it.

SELECT name, email
FROM Students
WHERE student_id = 101;

This query asks for two columns from the row whose identifier is 101. A selection condition belongs in WHERE. To order results, use ORDER BY; to limit the number of rows, use a database's supported limiting syntax.

Insert, update, and delete

INSERT INTO Students (student_id, name, email)
VALUES (103, 'Lee', 'lee@example.test');

UPDATE Students
SET email = 'lee.new@example.test'
WHERE student_id = 103;

DELETE FROM Students
WHERE student_id = 103;

An update or delete without a sufficiently specific condition can affect more rows than intended. Test data changes carefully, use constraints, and check the number of affected records where the database API supports it.

Use parameters for values from users

When application input is part of a query, use the database library's parameterized-query interface. Parameters keep data separate from SQL syntax and help prevent user-provided text from being interpreted as instructions. Avoid constructing SQL by concatenating untrusted strings.

Connect related data with joins

A join combines rows from related tables. The enrollment and student records can be combined by matching their shared student identifier:

SELECT Students.name, Enrollments.course_id, Enrollments.status
FROM Students
JOIN Enrollments
  ON Students.student_id = Enrollments.student_id
WHERE Enrollments.status = 'active';

An inner join returns rows with matches on both sides. A left join keeps every row from the left table and fills missing right-side values with NULL. The join type affects the result, so choose it based on which records should remain when a relationship is missing.

Joins can produce many rows when relationships are one-to-many or many-to-many. Understanding the keys and cardinality helps prevent accidental duplicates and incorrect counts.

Summaries with grouping

Aggregate functions summarize groups of rows. A query can count active enrollments per course like this:

SELECT course_id, COUNT(*) AS active_count
FROM Enrollments
WHERE status = 'active'
GROUP BY course_id
ORDER BY active_count DESC;

The WHERE condition filters rows before grouping; GROUP BY forms groups; the aggregate computes a result for each group. A HAVING condition can filter groups based on an aggregate. These clauses express the question, while the database plans how to read the underlying data.

When counting through joins, check whether one source record can match multiple rows. Joining a student to several enrollments duplicates the student in the result. Counting rows after that join counts enrollments, not unique students. Choosing the correct key and aggregation is part of defining the result accurately.

Transactions and the ACID properties

A transaction groups operations that should be treated as one logical unit. For a transfer between two accounts, subtracting from one account and adding to the other should not leave the database halfway updated if the process fails.

Relational database transactions are often described by ACID:

• Atomicity: all transaction changes happen, or none do.
• Consistency: committed changes preserve database rules.
• Isolation: concurrent transactions do not interfere in unintended ways.
• Durability: committed changes survive failures according to the system's guarantees.

Exact isolation and durability guarantees depend on the database and configuration. Applications should understand the chosen isolation level, error handling, and commit behavior rather than assuming every system has identical semantics.

Worked example: transfer as one transaction

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 25 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 25 WHERE account_id = 2;

COMMIT;

If a required step fails, the application should roll back rather than commit a partial transfer. Production code also validates account existence, permissions, sufficient funds, currency rules, and concurrent updates. The database transaction is one part of the complete business rule.

Concurrent transactions and isolation

Two users may read and update the same records at nearly the same time. Without coordination, one update might overwrite another, or a query might see a mixture of old and new values. Isolation levels describe which kinds of concurrent effects a transaction can observe. Stronger isolation can prevent more anomalies but may reduce concurrency or require retries.

A common example is two shoppers trying to purchase the final item. Both might see a stock count of one. The database operation must prevent both orders from successfully claiming that same item. This can be handled with a conditional update, locking, or another database-supported concurrency mechanism. The right method depends on the system and its transaction guarantees.

Applications should be prepared for transactions to be rejected, blocked, or retried. A retry should repeat a complete, safe unit of work and should not send duplicate external actions such as email or payment without an idempotency strategy.

Constraints are executable business rules

Checks, unique constraints, and foreign keys let the database reject invalid states even if two different application paths attempt to write them. For example, a nonnegative-balance check can protect against an accidental negative value. Constraints do not replace application-level validation: the application still needs to explain errors to users and enforce rules that involve external systems.

Indexes and query performance

An index is an auxiliary structure that helps a database find rows without scanning an entire table. Many indexes use a tree-like structure; hash indexes are also used in some systems. An index can make lookups and ordering faster, but it consumes storage and adds work to inserts, updates, and deletes.

Query pattern	Possible index	Trade-off
Find student by ID	Primary-key index	Usually created with the primary key
Find records by email	Index on email	Useful if lookups are frequent and selective
Filter by status and sort by date	Potential composite index	Column order and query shape matter
Small table scanned entirely	Maybe no extra index	An index may cost more than it saves

Use query plans and realistic measurements to find slow operations. Adding indexes indiscriminately can slow writes and consume memory. A database optimizer may choose not to use an index if scanning is cheaper.

Composite indexes and selectivity

A composite index stores entries using more than one column. An index on (status, created_at) may help queries that filter by status and then order or range-filter by date. Many database systems can use the leading portion of a composite index, but an index beginning with created_at may serve different queries. Consult the database's documentation and query plan rather than assuming columns are interchangeable.

An index is most useful when it narrows a large set of candidates. If a column has only a few possible values, an index on that column alone may not save much work. Query plans reveal whether the engine scans a table, uses an index, or chooses another strategy. A plan describes estimated or actual work; it should be interpreted with representative data.

Pagination for large results

Returning every matching row can use too much memory and take too long. Pagination returns a manageable portion at a time. Offset-based pagination is straightforward, but large offsets may require scanning past many rows, and changing data can shift page boundaries. Keyset pagination uses a stable ordering key to continue after the last result and can be more efficient for large, frequently updated data sets.

Other database models

Not every workload fits a relational table model equally well. Database families make different trade-offs; the label “NoSQL” covers several distinct models rather than one specific replacement for SQL.

• Key-value: look up a value by a key; useful for caches and simple mappings.
• Document: store records as structured documents; useful when fields vary or are nested.
• Wide-column: organize data for large distributed workloads with particular query patterns.
• Graph: store entities and relationships directly for connection-heavy queries.
• Relational: represent structured relationships with tables, constraints, and expressive joins.

Choose based on access patterns, consistency needs, data shape, operational expertise, and failure requirements. A fashionable technology does not remove the need to model the problem.

Backups, privacy, and reliability

A database is valuable only if its data is protected and recoverable. Backups should be stored separately from the system they protect, access should be limited, and restoration should be tested. Replication can improve availability, but a replica may copy accidental deletion or corruption; it is not automatically a backup.

Collect only the information an application needs, protect sensitive values, and define retention rules. Use access controls and encryption supported by the platform. Logs and backups can contain private information too, so treat them as part of the data system.

Operational planning considers failure modes: disk loss, software bugs, accidental changes, network partitions, and unavailable services. Monitor errors and capacity, and define how the application behaves when the database cannot be reached.

Backups need a recovery plan

Different backup methods trade off storage space, recovery time, and how much recent data can be lost. A full backup copies a complete data set; incremental or log-based methods can capture later changes. A recovery plan defines the acceptable recovery point (how much recent work could be lost) and recovery time (how long restoration can take).

Test restores on a schedule and record the steps. Verify not only that files exist, but that the restored database can start, contains expected data, and can serve an application. Keep backup credentials separate from ordinary application access so a compromised application account cannot automatically erase every recovery copy.

Compact glossary

Schema

A description of a database's structure, data types, relationships, and constraints.

Primary key

A value or set of values that uniquely identifies a row.

Foreign key

A constraint linking a value to a key in another table.

Transaction

A group of database operations treated as one logical unit.

Index

An auxiliary structure that can speed up selected queries at a storage and write cost.

Normalization

Principles for organizing relational facts to reduce problematic duplication.

References and further reading

1. Silberschatz, Korth, and Sudarshan. Database System Concepts, 7th edition. McGraw Hill, 2019. Author resources.
2. PostgreSQL Global Development Group. PostgreSQL Documentation: The SQL Language. Tutorial.
3. SQLite. Appropriate Uses For SQLite. Design guidance.
4. Martin Kleppmann. Designing Data-Intensive Applications. O'Reilly Media, 2017. Book information.

Try it yourself

1. Design tables for books, authors, and a many-to-many book-author relationship. Identify keys.
2. Write a query that selects active enrollments and their students' names.
3. Why should the two updates in a money transfer share a transaction?
4. Which index might help frequent lookups by email, and what cost does it add?
5. Explain why replication and backup solve different reliability problems.

Remember: model facts clearly, enforce important rules, and measure real queries before optimizing storage.