Updated July 29, 2026:We added the roles of analytics platforms and vector search among database types, and reorganized design, migration, backup, and restore testing into a sequence of decisions. This article contains no advertising or affiliate links.
A database is not just a box for storing data.It uses structures and rules to preserve what is recorded, what must be unique, which data is related, what concurrent updates must protect, and how far back to recover after a failure.
A database is a foundation for retrieving answers while preserving facts, relationships, and update rules.
Instead of starting with products or SQL, first define business terms such as customers, products, and orders; identifiers, relationships, constraints, representative queries, concurrent updates, RPO, and RTO. A DBMS is the software that implements that design and controls retrieval, updates, permissions, and recovery.
| In one sentence | A way to store data according to structures and rules while preserving relationships and integrity through searches, additions, updates, and deletions |
|---|---|
| Common elements | Schemas, types, primary and foreign keys, constraints, SQL, transactions, indexes, permissions, logs, and backups |
| Common uses | Members, orders, inventory, accounting, reservations, posts, settings, logs, analytics, sensors, and recommendations |
| Common types | Relational, document, key-value and data structures, graph, time series, embedded, and analytics platforms |
| Selection | Consider relationships, integrity, queries, concurrency, recovery, and operational capability as well as volume |
| Facts checked | July 29, 2026 |
What is a database? Start with a one-sentence definition
An online store, for example, records customers, products, orders, order items, inventory, and delivery addresses. Storage alone is not enough. The application and DBMS must continually enforce rules such as unique customer IDs, orders linked to existing customers, quantities greater than zero, and order confirmation and stock reduction not succeeding independently halfway through.
Database, DBMS, SQL, and server: the differences
Separating similar terms clarifies where failures occur, costs, and operational responsibilities. SQL is not the database itself; it is a language used mainly to communicate definitions and operations to a relational DBMS.
| Term | Role | Examples and notes |
|---|---|---|
| Database | A managed collection of data with structure, relationships, and rules | Customer databases, order databases, and WordPress site data |
| DBMS | Creates, queries, and updates databases and controls constraints, concurrency, permissions, logs, and recovery | PostgreSQL, MySQL, MongoDB, SQLite, and others |
| SQL | A language for table definitions, queries, inserts, updates, deletes, permissions, and transactions | Common concepts exist, but types, functions, JSON, indexes, and more vary by product and version |
| Database server | A process or computing environment that runs a DBMS and accepts connections and operations from applications | Not necessarily one physical machine; it may be a cluster or managed service |
A cloud managed database lets a provider handle some work such as DBMS setup, patches, replication, and backups. It does not automatically make schemas, queries, permissions, retention, recovery goals, or application defects correct.
How tables, rows, columns, primary keys, and foreign keys work
Relational databases separate tables by entity, with one fact per row and attributes in columns. For customers and orders, a customer ID expresses the relationship instead of repeatedly copying the customer's name and address into the order table.
| Table | A grouping for entities of the same kind, such as customers, orders, or products |
|---|---|
| Row (record) | Data for one entity or fact, such as one customer or order |
| Column | An attribute such as a name, amount, timestamp, or status, with a defined type and NULL policy |
| Primary key (PK) | A column or combination of columns that uniquely identifies each row and cannot be duplicated or NULL |
| Foreign key (FK) | A relationship constraint that references a key in another table, preventing, for example, orders for nonexistent customers |
| Constraint | A rule for permitted data, such as NOT NULL, UNIQUE, CHECK, a primary key, or a foreign key |
Separate responsibilities
Separating customers, products, orders, and order items reduces duplication and missed updates. Decide the level of separation based on both integrity and access patterns.
Values may also be duplicated as history
A design may save the sale-time unit price and delivery address on an order so that past orders remain reproducible when current product or customer values change. Not all duplication is harmful.
Basics of querying, adding, updating, and deleting with SQL
The basic data operations are called CRUD, for Create, Read, Update, and Delete. In SQL, define the table and then use commands such as these. Syntax and types differ by DBMS, so consult the official documentation for the version you use.
-- Create: add an order
INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (1001, 42, 3980);
-- Read: find customer 42's orders, newest first
SELECT order_id, total_amount, ordered_at
FROM orders
WHERE customer_id = 42
ORDER BY ordered_at DESC;
-- Update: change the order status
UPDATE orders
SET status = 'paid'
WHERE order_id = 1001;
-- Delete: verify the target before deleting
DELETE FROM orders
WHERE order_id = 1001;Real work also involves joins, aggregation, pagination, full-text search, JSON processing, and batch updates. Query patterns directly affect table and index design.
Transactions, ACID, constraints, and indexes
Confirming an order involves several updates, including saving the order and its items and reducing stock. Group related updates in a transaction because one succeeding alone can create an inconsistency.
Validate the customer, product, quantity, price, currency, stock, and duplicate orders.
Treat the order, line items, and stock as one business operation and control necessary concurrent updates.
Reject nonexistent products, insufficient stock, invalid state transitions, and duplicate keys.
ROLLBACK if any step fails. External payments and delivery are outside the database transaction, so design idempotency, retries, and compensating actions separately.
Treat multiple updates as either all successful or all undone
Move to a valid state that satisfies defined constraints and business rules
Control concurrent operations so intermediate states do not improperly affect one another
Preserve or recover committed results after failure
Constraints are a final safeguard against invalid data
Application input validation is important for usability, but other APIs, administration tools, batches, and migrations also write data. DBMS constraints for required values, uniqueness, ranges, and references help preserve rules across write paths.
Indexes speed queries, but too many make updates more expensive
Like a book index, a database index provides a shortcut to matching rows. It also uses storage and must be updated on inserts, updates, and deletes. Add indexes based on filters, column order, selectivity, ordering, and result size, using execution plans and measurements.
Database types and suitable uses
Choosing only between SQL and NoSQL can overlook actual reads and writes, consistency, and operational work. One product may offer JSON, full-text search, time series, graph, or vector search, so representative queries and guarantees matter more than category names.
| Type | Main structure | Suitable uses | Design considerations |
|---|---|---|---|
| Relational | Tables, rows, columns, primary and foreign keys, constraints, and SQL | Business data such as orders, accounting, inventory, customers, and reservations where relationships and integrity matter | Design normalization, constraints, transaction boundaries, joins, indexes, and schema changes |
| Document | JSON-like documents and collections, with nesting and arrays | Catalogs with attributes that vary by product, hierarchical data read together, and flexible content structures | Choose embedding, references, document size, duplication, concurrent updates, and validation based on access patterns |
| Key-value and data structures | Retrieve values by key; strings, hashes, sets, streams, and more | Caches, sessions, counters, rankings, queues, and real-time state | Check persistence, expiration, memory, eviction behavior, consistency with the source of truth, and limits on complex searches |
| Graph | Nodes, relationships, direction, and properties | Connection-centered questions such as routes, dependencies, inherited permissions, fraud networks, and recommendations | Define node and relationship granularity, traversal depth, direction, constraints, update frequency, and representative queries |
| Time series | Timestamped observations, tags, period aggregation, and retention | Large chronological datasets such as monitoring metrics, sensors, prices, and equipment operation | Decide timestamp precision, late and duplicate data handling, retention, downsampling, and recalculation |
| Embedded | Manage files on or near the same device or process as the application | Mobile, desktop, single-device, local-first applications, and testing | Check multi-device synchronization, encryption, lost devices, backups, migration, and concurrent connections |
| Analytics platform | Column-oriented and distributed processing, integrated history, and structures optimized for aggregation | Cross-department BI, long-term history, large aggregations, and machine-learning data | Manage separation from operational databases, ingestion delay, inconsistent definitions, quality, personal data, and cost |
| Vector search | Embedding vectors and indexes for nearest-neighbor search | Semantic search, similar documents and images, recommendations, and retrieval for RAG | Not necessarily a standalone product. Design source data, metadata filtering, updates, evaluation, and permissions together |
Vector search is not a new universal category of database.It may be added to existing relational, graph, or key-value products. Check synchronization with source data, access control, propagation of deletions, re-embedding, and cost as well as search quality.
How databases differ from Excel, files, caches, and data warehouses
Not every tool with a different purpose needs to be replaced by a database. Small registers edited directly by people, images and documents, temporary data for speed, and historical data for analysis each have appropriate homes.
| Technology | Strengths | How it complements a database |
|---|---|---|
| Excel and spreadsheets | People edit cells directly and use formulas, summaries, and charts for small registers and analysis | Useful for flexible work by small groups, but limited as a source of truth for concurrent updates by multiple applications, referential integrity, fine-grained permissions, and APIs |
| CSV, JSON, and other files | Exchange, distribution, backup, batch input, and portability | Constraints, concurrent updates, indexes, and transactions across files require separate arrangements |
| Object storage | Stores large binary objects such as images, videos, PDFs, and backups | A common design stores the objects in storage and filenames, owners, visibility, and searchable metadata in a database |
| Cache | Keeps copies of source data or computed results nearby to improve response time | Not usually the source of truth. Handle expiration, invalidation, refetch after misses, and inconsistencies with updates |
| Search engine | Full-text search, relevance, facets, and log exploration | Often indexes the authoritative database. Design for synchronization delay, deletion propagation, and permission filters |
| Data warehouse | Integrates and retains history from multiple systems, optimized for large-scale analysis | Serves a different purpose from an operational database processing business updates. Manage ingestion, quality, definitions, and cost |
The role of databases in WordPress
WordPress stores much more than post content in its database, including users, comments, categories, tags, menus, widgets, site settings, and plugin settings. Images, themes, plugin code, and configuration files also reside in the filesystem.
Content and settings
- Posts, pages, and custom posts
- Users, permissions, and comments
- Categories, tags, and associations
- Settings including URLs and plugins
Assets and code
- Uploaded images, videos, and PDFs
- Theme and plugin code
- WordPress core
- Configuration files such as wp-config.php
Restore the database and files to the same point in time
Restoring only the database can leave images missing; restoring only files may not recover posts or settings. For migration or disaster recovery, test one procedure covering the database, uploads, themes, plugins, configuration, permissions, domain, HTTPS, and email.
Choosing a database: seven decision factors
Choose based on fit for your data, queries, failure requirements, and operational ability rather than popularity or peak performance. Small representative datasets and core operations measured on candidate products help inform the decision.
Entities, relationships, and history
Define entities such as customers, contracts, products, and orders, identifiers, one-to-many and many-to-many relationships, change history, and audit records after deletion.
Types, constraints, and integrity
Decide required values, duplication, ranges, state transitions, foreign keys, amounts, currencies, and timestamps, separating DBMS and business-logic responsibilities.
Queries and access patterns
List filters, ordering, joins, aggregation, result counts, and target response times for screens, APIs, searches, reports, and batches.
Concurrent updates and transactions
For stock, balances, reservations, and state changes, identify what conflicts must preserve and how retries, idempotency, and partial failure are handled.
Capacity, load, and latency
Measure present and future counts, sizes and growth, read/write ratios, peak TPS, concurrent connections, long-running queries, and network behavior.
Availability, RPO, RTO, and recovery
Define acceptable downtime and data loss, replication, failover, backups, retention, separate failure domains, and restore tests.
Permissions, operational capability, and total cost
Compare least privilege, encryption, auditing, personal data, patching, monitoring, migration, team experience, licenses, cloud costs, and lock-in.
Use a relational model as a baseline for business data
For orders, stock, reservations, and accounting with many relationships, constraints, transactions, and aggregations, a relational DBMS is an understandable starting point. Consider another model when you can identify why it better meets the requirements.
Choosing a fashionable product first
Fixing the product first can force the data model and operations to fit it. Narrow candidates using representative queries, integrity, recovery, and team capabilities.
Six stages for more reliable introduction, migration, and operations
Rather than copying existing Excel or screen fields straight into tables, describe business facts and rules and test with small datasets before migrating.
- Define business terms and sources of truth
Document the meanings, identifiers, states, timestamps, and responsible departments for customer, contract, revenue, and similar terms, and choose the authoritative system.
- Design models, keys, and constraints
Use representative data and exceptions to design tables, documents, relationships, primary and foreign keys, required values, duplicates, deletion, history, and personal data.
- Prototype key reads and writes
Implement representative queries and updates for screens, APIs, reports, and batches, and test concurrency, retries, duplicates, and partial failures.
- Measure load with representative data volumes
Measure execution plans, response times, CPU, memory, I/O, connections, locks, and index size, recording targets separately from measured values.
- Rehearse migration, cutover, and recovery
Reconcile totals, relationships, duplicates, missing values, characters, and times as well as counts; verify rollback and restoration into a separate environment.
- Monitor and change safely
Monitor latency, errors, connections, capacity, replication delay, and backup results, and plan compatibility, long locks, and rollback for schema changes.
Pre-launch checklist
- Primary keys and constraints for required values, uniqueness, references, and state transitions have been checked
- Application, administration, and analytics permissions have been separated
- Key queries have been measured with representative data and peak conditions
- A backup has been restored into a separate environment and application connectivity checked
- Schema changes, rollback, incident contacts, and responsibilities have been documented
Common misconceptions and mistakes
Without designing types, constraints, permissions, input validation, and transaction boundaries, errors and inconsistencies can still be stored.
Although declaration methods differ, fields, types, relationships, validation, migration, and access patterns still need design.
Indexes may help reads but add update, storage, and maintenance overhead. Review unused indexes regularly.
Mistakes and defects are replicated too. Separate point-in-time backups and restore tests are necessary.
This can expose personal and secret data. Define anonymization, minimization, permissions, and retention limits.
Synchronization, failure handling, monitoring, and backups become more complex. Separate systems only after measuring requirements one system cannot meet.
Frequently asked questions about databases
Are a database and a DBMS the same thing?
Strictly speaking, no. A database is a managed collection of data; a DBMS is software that creates, retrieves, and updates it while controlling constraints, concurrency, permissions, logs, and recovery.
Are Excel and spreadsheets databases too?
They can be used as databases in a broad sense, but differ from typical DBMSs in capabilities and purpose. They are useful for small registers and analysis; consider a DBMS when multiple applications need concurrent updates, referential integrity, fine-grained permissions, or transactions.
Are all databases that use SQL the same?
No. They share concepts, but types, functions, indexes, transactions, JSON, permissions, backups, and extensions vary by product and version.
Does NoSQL eliminate the need for schema design?
No. Even without a fixed schema declared in advance, design fields, types, required values, identifiers, relationships, duplication, and change procedures, and share them between application and operations teams.
Will more indexes always make searches faster?
Not necessarily. They speed suitable searches but increase storage and insert, update, and delete overhead. Choose them by measuring execution plans and response times with representative data.
Is a database backup alone enough for WordPress?
Usually not. Posts and settings are in the database, but uploaded images, themes, plugins, and configuration files also reside in the filesystem. Restore both to a consistent point and verify operation.
Does a small website need a database?
A static company information site does not necessarily need one. A database becomes more useful when data such as inquiries, members, reservations, products, or articles must be added, searched, and updated regularly.
Is a cloud database automatically secure?
A provider can handle some operations, but security depends on configuration. Check exposure, authentication, least privilege, encryption, auditing, backups, recovery, and application SQL implementation.
Official sources reviewed
We checked the following official documentation on July 29, 2026 to distinguish general concepts from product-specific implementations. When adopting a product, also consult documentation for its version and cloud configuration.
- PostgreSQL Documentation — Tutorial: tables, queries, joins, updates, foreign keys, and transactions
- PostgreSQL Documentation — Constraints: CHECK, NOT NULL, UNIQUE, primary keys, and foreign keys
- PostgreSQL Documentation — Transactions: transaction basics
- PostgreSQL Documentation — Introduction to Indexes: benefits and costs of indexes
- MySQL Reference Manual — InnoDB and the ACID Model: ACID properties
- MongoDB Manual — Data Modeling: document embedding, references, and access patterns
- Redis Docs — Data types: key-value, data structures, and vector sets
- Neo4j Documentation — What is a graph database?: nodes, relationships, and properties
- WordPress Developer Resources — Creating Database for WordPress: preparing a database and user for WordPress
Design a database around the facts to preserve and questions to answer, not storage volume
A database stores data under structures and rules, preserving relationships and integrity as it is queried and updated. A DBMS controls constraints, transactions, concurrency, indexes, permissions, logs, and recovery. Define business terms and authoritative sources for customers, orders, and products, then define keys, constraints, representative queries, update boundaries, performance goals, RPO and RTO, retention, and deletion.
Test inconsistencies and concurrent updates with small examples, measure representative data volumes, and successfully restore into a separate environment to build a durable data foundation.