Database Review: SQL, Normalization, NoSQL, and Common Database Types

Database review should go beyond “SQL supports CRUD and NoSQL is flexible.” Real systems must answer harder questions: why did a JOIN inflate a total? What happens to old orders when prices change? Can two customers buy the last item? Why do replicas not replace backups?

This article uses a small order system to connect modeling, querying, performance, and concurrency. SQL examples use PostgreSQL and basic capabilities shared by PostgreSQL 14 and later; most versioned references point to PostgreSQL 17. MySQL differences are discussed separately. MongoDB, Redis, and graph examples illustrate their own data models.

Run the schema and seed data in an isolated practice database. Later write demonstrations roll back to preserve that data. The checkout example describes steps whose results and transaction boundaries must be controlled by an application; its comments are essential, and it is not a complete checkout program.

1. Derive a data model from business operations

1.1 Start with relationships and invariants

The system must let users view their orders, purchase several products in an order, decrement inventory at checkout, and report paid sales. All amounts here are AUD, excluding taxes, discounts, and refunds.

users       1 ───── N orders
orders      1 ───── N order_items
products    1 ───── N order_items

order_items connects orders and products and records quantity and the price agreed at purchase. We assume one row per product per order, so (order_id, product_id) uniquely identifies a line. If separate batches or promotions allow the same product on multiple lines, introduce a line ID rather than forcing this key onto a different business rule.

State the constraints first: every order belongs to a user, every line references an existing order and product, quantities are positive, and prices and stock are nonnegative. Database constraints protect all writers; application validation can provide better feedback. Both layers are useful.

1.2 Schema: types and constraints have distinct responsibilities

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
 
CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0),
  version INTEGER NOT NULL DEFAULT 1 CHECK (version > 0)
);
 
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL,
  paid_at TIMESTAMPTZ,
  CHECK (
    (status = 'paid' AND paid_at IS NOT NULL)
    OR (status <> 'paid' AND paid_at IS NULL)
  )
);
 
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

Explicit IDs make the results easy to inspect. Production systems can use identity columns, sequences, or other ID strategies. NUMERIC represents exact decimal money instead of approximate floating point; an integer count of minor currency units is another option. Multiple currencies require currency identifiers and rounding rules, not a sum of unrelated units.

In PostgreSQL, a CHECK evaluating to unknown can pass, so CHECK (stock >= 0) does not replace NOT NULL. Primary and unique constraints create corresponding unique indexes; referencing foreign-key columns do not necessarily receive the indexes needed by queries. See PostgreSQL constraints.

This example does not cascade deletion through historical orders. Default foreign-key behavior prevents deleting a referenced product. Taking a product off sale can instead be a state change. The order-status check is also a teaching convention; real payment and refund states require their own model.

1.3 One dataset for the whole article

INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', 'alice@example.com'),
  (2, 'Bob', 'bob@example.com'),
  (3, 'Chen', 'chen@example.com');
 
INSERT INTO products (id, name, price, stock) VALUES
  (10, 'Keyboard', 120.00, 5),
  (11, 'Mouse', 50.00, 8),
  (12, 'Stand', 40.00, 1);
 
INSERT INTO orders (id, user_id, status, created_at, paid_at) VALUES
  (101, 1, 'paid', '2026-05-20 10:00:00+00', '2026-05-20 10:05:00+00'),
  (102, 1, 'pending', '2026-05-21 09:00:00+00', NULL),
  (103, 2, 'paid', '2026-05-21 11:00:00+00', '2026-05-21 11:05:00+00'),
  (104, 2, 'paid', '2026-05-21 12:00:00+00', '2026-05-21 12:05:00+00');
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (101, 10, 1, 100.00),
  (101, 11, 2, 50.00),
  (102, 12, 1, 40.00),
  (103, 11, 1, 50.00),
  (104, 11, 1, 50.00);

Order 101 totals 1 × 100 + 2 × 50 = 200. Order 102 totals 40 and is unpaid. Orders 103 and 104 total 50 each, so paid orders total 300.

The keyboard currently costs 120, but its historical sale price was 100. products.price describes today's offer; order_items.unit_price records a transaction fact. These are not duplicate fields that must remain synchronized. Sample stock is a current inventory snapshot, not opening inventory to be recalculated from these few historical orders.

2. SQL: establish correctness before optimizing

2.1 Filtering, ordering, and NULL

SELECT id, status, created_at
FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
SELECT id
FROM orders
WHERE paid_at IS NULL
ORDER BY id;

The first query returns 101; the second returns 102. Without ORDER BY, do not rely on row order. The ID breaks timestamp ties explicitly.

NULL represents missing or unknown information. Use IS NULL, not paid_at = NULL, to find it. COUNT(*) counts rows, while COUNT(paid_at) counts non-null values, giving 4 and 3 here. A NOT IN subquery containing NULL can also introduce unexpected unknown results; consider NOT EXISTS when expressing the absence of a match.

2.2 JOIN changes the grain of the result

SELECT o.id AS order_id, p.name, oi.quantity, oi.unit_price,
       oi.quantity * oi.unit_price AS line_amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.id = 101
ORDER BY oi.product_id;

Each result row now represents an order line, not an order. This change matters when aggregating later.

INNER JOIN retains matching rows. To include users who have never ordered, use LEFT JOIN:

SELECT u.id, u.name, COUNT(o.id) AS paid_order_count
FROM users AS u
LEFT JOIN orders AS o
  ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY u.id;

The results are Alice 1, Bob 2, and Chen 0. Keeping the status predicate in ON preserves unmatched users; placing it in WHERE would reject their null order values. COUNT(*) would also be wrong because Chen still has a result row. See PostgreSQL table expressions and joins.

2.3 GROUP BY, WHERE, and HAVING

SELECT o.id, SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id
HAVING SUM(oi.quantity * oi.unit_price) >= 100
ORDER BY o.id;

Only order 101 appears, with amount 200. WHERE excludes unpaid orders, GROUP BY collects lines into order groups, and HAVING filters their aggregated amounts.

Daily sales also need a business definition: creation day or payment day, which time zone, and whether refunds are deducted. This query sums paid line amounts by UTC payment date:

SELECT (o.paid_at AT TIME ZONE 'UTC')::date AS paid_day,
       SUM(oi.quantity * oi.unit_price) AS sales_amount
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY (o.paid_at AT TIME ZONE 'UTC')::date
ORDER BY paid_day;

The results are 200 for 2026-05-20 and 100 for 2026-05-21. Time-zone conversion and ::date use PostgreSQL syntax. A financial report must also account for refunds and other relevant amounts; this simplified measure is not automatically net revenue.

2.4 Duplicate aggregation: DISTINCT is not a universal repair

The following query is intentionally wrong. Computing order totals and then joining lines repeats order 101's total twice:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS amount
  FROM order_items
  GROUP BY order_id
)
SELECT SUM(t.amount) AS wrong_total,
       SUM(DISTINCT t.amount) AS also_wrong_total
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status = 'paid';

It returns 500 and 250; the correct value is 300. SUM(DISTINCT amount) also collapses two genuinely different orders worth 50 each. DISTINCT deduplicates values, not the business entities you intended to count.

Keep one row per order in the total calculation by removing the unnecessary line join:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS amount
  FROM order_items
  GROUP BY order_id
)
SELECT SUM(t.amount) AS paid_total
FROM orders AS o
JOIN order_totals AS t ON t.order_id = o.id
WHERE o.status = 'paid';

Establish the grain of each intermediate result before choosing DISTINCT, subqueries, or pre-aggregation. That is usually more effective than repeatedly changing aggregate functions.

2.5 EXISTS expresses “at least one”

Finding users who bought a keyboard does not require returning their individual order lines:

SELECT u.id, u.name
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.id
  WHERE o.user_id = u.id
    AND o.status = 'paid'
    AND oi.product_id = 10
)
ORDER BY u.id;

Only Alice is returned. EXISTS expresses existence, so buying ten times would not create ten outer rows. Whether it outperforms an equivalent formulation depends on the plan and data.

2.6 CTEs and window functions: each user's latest order

WITH ranked_orders AS (
  SELECT id, user_id, status, created_at,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders
)
SELECT user_id, id, status
FROM ranked_orders
WHERE rn = 1
ORDER BY user_id;

The results are Alice's 102 and Bob's 104. Chen has no order and does not appear. The window function preserves rows and numbers them within user groups; the outer query selects the first. It does not collapse all orders into a single aggregate row. See the PostgreSQL window function tutorial.

For the latest paid order, filter status = 'paid' before numbering. Filtering outside would instead ask whether the latest order happens to be paid and would omit Alice. A CTE helps express steps clearly, but does not guarantee materialization or better performance.

2.7 Writes, affected rows, and parameters

This demonstration rolls back, leaving the data unchanged for later examples:

BEGIN;
 
INSERT INTO users (id, name, email)
VALUES (4, 'Diana', 'diana@example.com');
 
UPDATE users
SET name = 'Alice Chen'
WHERE id = 1
RETURNING id, name;
 
DELETE FROM users
WHERE id = 4
RETURNING id;
 
ROLLBACK;

Use stable IDs to identify rows rather than assuming names are unique. Inspect affected-row counts: zero instead of one may indicate deletion or a failed concurrency condition; several rows may expose an incorrect WHERE clause.

Applications should bind parameters through their driver. PostgreSQL drivers commonly accept $1 placeholders with a separate parameter array. Do not concatenate user input or assume an ORM prevents unbounded updates. RETURNING is a PostgreSQL capability used here; adapt the syntax and driver behavior when moving to MySQL.

3. Normalization: understand anomalies before memorizing normal forms

3.1 Define keys and functional dependencies

Suppose one large table mixes order, user, and product data:

order_id, product_id, user_id, user_name, email,
product_name, quantity, unit_price

Under the one-product-line-per-order assumption, (order_id, product_id) is a candidate key. A candidate key is a minimal set of attributes that uniquely identifies a record. The primary key is one selected candidate; normalization must consider other candidates too.

Assume these business dependencies hold:

order_id                → user_id
user_id                 → user_name, email
product_id              → product_name
(order_id, product_id)  → quantity, unit_price

An arrow means that the left side determines the right side, not that today's sample happens to have no duplicates. Keeping all of these facts together creates update anomalies when an email appears in many rows, insertion anomalies when a product has no order yet, and deletion anomalies when removing its last line loses its product information.

3.2 First normal form: avoid repeating column groups

Columns named product1, product2, and product3 make a fourth product a schema change. A comma-separated product list makes individual quantities, constraints, and queries difficult. Separate line rows fit this business model better.

Atomic does not mean splitting every string into characters. It depends on the chosen domain and required operations. Splitting a person's name and representing an order's multiple lines are separate modeling questions. Array and JSON support does not automatically replace relationships requiring foreign keys and per-item constraints.

3.3 Second normal form: remove dependencies on part of a candidate key

2NF requires 1NF and excludes dependencies of non-prime attributes on a proper subset of a candidate key. A prime attribute belongs to at least one candidate key.

In the large table, product_name depends on product_id, while user_id depends on order_id; neither needs the complete composite key. Move product facts into products and order-level facts into orders, retaining quantity and sale price on the lines. This removes those partial dependencies.

Adding an auto-incrementing ID does not erase the business dependencies. Analyze the real candidate keys and determinants rather than using a surrogate key to conceal repeated facts.

3.4 Third normal form: separate facts about different entities

If orders still stores user_id, user_name, email, the dependency order_id → user_id → email remains. Current user details belong in users, referenced by the order.

Formally, for every nontrivial functional dependency X → A, 3NF requires X to be a superkey or A to be prime. For this simple model, “keep non-key facts with their entity and avoid indirect determination through another non-key attribute” is a useful introduction, not a complete definition for every schema.

A decomposition should also allow a lossless reconstruction of the original relation and, where possible, preserve dependencies that can be enforced directly. More tables do not inherently mean better normalization, and reaching 3NF does not guarantee faster queries.

3.5 Historical snapshots are different from performance redundancy

A sale price or shipping-address snapshot represents a fact at checkout. It should not track today's price or the user's current address. That is not merely duplicated data retained for speed.

Storing a derived order total or comment count for display is a different choice. Specify who updates it, whether updates share a transaction with source facts, how much delay is acceptable, and how to recalculate or reconcile it.

Start with a clear fact model, then introduce summary tables or materialized views when measurements justify them. Denormalization with a read-speed benefit but no maintenance strategy often moves complexity into failure recovery.

4. Indexes and execution plans: optimize an access path

4.1 Read benefits come with maintenance costs

A B-tree index maintains ordered keys that help locate records and commonly supports equality, range, and ordered access. It is not a copy of the table that is always faster to read.

An index is more likely to help when a query needs a small fraction of rows. Scanning most of a table may favor sequential access. Inserts, deletes, and changes to indexed values maintain the relevant structures and consume storage, cache space, and write bandwidth.

Unique indexes can also enforce uniqueness; ordinary query indexes mainly provide access paths. Having a primary-key index does not optimize every other lookup automatically.

4.2 Choose composite keys for filtering and ordering together

CREATE INDEX orders_user_status_created_idx
ON orders (user_id, status, created_at DESC, id DESC);
 
SELECT id, created_at
FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;

The query fixes a user and status, then selects recent rows by timestamp and ID, making this index worth evaluating. Another query that omits status but sorts across all statuses may behave differently because status sits between user and time.

Leading columns commonly affect the scan range of a composite B-tree. However, “an index is impossible to use without its leftmost column” is too absolute. Optimizer capabilities, versions, index types, and distributions matter. Use PostgreSQL 17's documented behavior as the reference here, then inspect the plan. See multicolumn indexes.

PostgreSQL also supports partial indexes with predicates for selected subsets. Queries must satisfy the relevant predicate conditions; this syntax is not portable to every database. See partial indexes.

4.3 EXPLAIN shows a plan; ANALYZE measures execution

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Inspect scan type, estimated versus actual rows, loop counts, sorting, and buffer activity. Planner cost is not milliseconds; execution timings are measurements affected by cache state and instrumentation overhead. See the PostgreSQL EXPLAIN guide.

With only four orders, a sequential scan can be entirely reasonable. Disabling it to obtain an index screenshot does not demonstrate an improvement. Use realistic volume, representative parameters, and current statistics when evaluating performance.

EXPLAIN ANALYZE executes its statement. Analyze writes only in an isolated environment with an explicit transaction strategy. Even rollback does not imply that sequence changes or external effects are reversible.

4.4 Deep offsets and stable cursors

OFFSET 100000 LIMIT 20 can process many discarded rows even with an index. Sequential browsing can instead continue after the previous page's final ordering key:

SELECT id, created_at
FROM orders
WHERE user_id = 2
  AND status = 'paid'
  AND (created_at, id) < (TIMESTAMPTZ '2026-05-21 12:00:00+00', 104)
ORDER BY created_at DESC, id DESC
LIMIT 20;

This returns 103. Time and ID establish a deterministic order, and a cursor should also be bound to its filters. Cursor pagination is less convenient for arbitrary page jumps and does not create a snapshot across requests. Deletions, permission changes, and mutable sort fields still need defined behavior.

5. Transactions and concurrency: correct inventory is deliberate

5.1 Relate ACID to failures

The database enforces declared constraints and submitted operations. It does not invent an unimplemented rule such as “a paid order must have at least one line.” ACID consistency is also not a synonym for every consistency model discussed in distributed systems.

5.2 The last item: the read-then-write race

A: SELECT stock → 1
B: SELECT stock → 1
A: decides stock is sufficient and prepares an order
B: also decides stock is sufficient and prepares an order

If both requests use their old value to set stock to zero, the final value is nonnegative but two orders may exist. CHECK (stock >= 0) cannot detect this missing decrement.

For a single product, combine the condition and decrement in one statement:

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 12 AND stock >= 1
RETURNING id, stock, price;

The application must require one returned row. Zero rows means no product met the condition, so it must not proceed to create an order. Under PostgreSQL Read Committed, a competing update waits and rechecks relevant conditions on the updated row. Higher isolation can require serialization-failure handling instead of producing the same return path.

5.3 Keep reservation and order writes in one transaction

These steps purchase a stand, then roll back to preserve the practice data. The application must branch on the first UPDATE's result. The example price of 40 comes from the seed data; real code uses a price established by the server.

BEGIN;
 
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 12 AND stock >= 1
RETURNING id, stock, price;
 
-- Continue only if one row was returned; otherwise ROLLBACK and stop.
INSERT INTO orders (id, user_id, status, created_at)
VALUES (105, 1, 'pending', '2026-05-22 09:00:00+00');
 
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (105, 12, 1, 40.00);
 
-- In the application: COMMIT only after all required writes succeed.
ROLLBACK;

After reserving stock, create a pending order and confirm payment through a separate workflow. Do not hold database row locks while waiting for a person to pay or for a slow payment service. Expiration, duplicate callbacks, and repeated stock release require state checks and idempotency too.

A local transaction cannot automatically roll back a remote payment. One approach records an event within the local transaction and delivers it reliably afterward, with idempotency and reconciliation for cross-system failures.

5.4 Pessimistic locks, optimistic versions, and deadlocks

If a decision needs several product fields, a transaction can lock the row before inspecting it:

BEGIN;
SELECT id, stock, price
FROM products
WHERE id = 12
FOR UPDATE;
-- Validate and perform the related writes while holding the row lock.
ROLLBACK;

A row lock restricts competing writes, not every ordinary read. Acquire multiple product locks in a consistent order and keep transactions short. Still handle deadlocks by retrying the entire transaction. See PostgreSQL explicit locking.

An optimistic approach requires the previous version to match:

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 12 AND version = 1 AND stock >= 1
RETURNING id, stock, version;

Zero rows means the condition no longer holds. Re-read and decide whether to retry rather than removing the condition and overwriting. Every relevant write path must maintain the version. Optimistic control reduces the need for explicit advance locking, but the actual write still uses database locks.

5.5 Isolation levels depend on the implementation

If A reads stock twice while B commits a decrement between those reads, Read Committed can show A first 1, then 0. A Repeatable Read snapshot query can retain the old view, but a later write is not guaranteed to succeed. Interpret guarantees for the actual operation. See PostgreSQL transaction isolation.

For write skew, imagine two reviewers and a rule requiring at least one to stay on duty. Each transaction sees the other reviewer available and marks its own reviewer offline. They write different rows, so a stable snapshot alone may still permit a broken rule. Appropriate locking, constraint design, or serializable isolation is needed.

MySQL InnoDB normally defaults to Repeatable Read, while PostgreSQL defaults to Read Committed. Equal isolation-level names do not make snapshot and locking behavior interchangeable. See MySQL InnoDB isolation levels.

5.6 Transaction retries and request idempotency are different

Deadlocks and serialization failures generally require rollback and re-execution of the complete transaction logic, with bounded retries and backoff. If a client loses the commit response, it may not even know whether the transaction committed. Blindly creating again does not resolve that uncertainty.

A business request ID with a uniqueness constraint can help recognize repeated checkout, but it also needs request-content binding, stored results, and coordination with order creation. Transactions define local write boundaries; idempotency recognizes duplication across requests.

6. NoSQL: understand the model and its access patterns

6.1 Document databases: embedding versus references

An order often loads as a unit, so a bounded set of lines can fit inside a document. This example uses integer minor currency units rather than assuming ordinary JSON decimals are an exact database money type:

{
  "_id": "order_101",
  "userId": "user_1",
  "currency": "AUD",
  "status": "paid",
  "items": [
    { "productId": "product_10", "quantity": 1, "unitPriceCents": 10000 },
    { "productId": "product_11", "quantity": 2, "unitPriceCents": 5000 }
  ]
}

Embedding is worth considering when children are accessed with the parent, have bounded size, and share its lifecycle. References deserve consideration when data is shared, independently updated, or could grow without bound. See MongoDB modeling guidance.

Do not put a user's entire order history in an ever-growing user document. Likewise, copying current product details into every order creates synchronization work; a historical sale-price snapshot has different semantics.

MongoDB supports atomic single-document operations and multi-document transactions. Evaluate deployment requirements, read/write guarantees, and cost rather than claiming document databases lack transactions. See MongoDB transactions.

6.2 Redis: caching is one use, not its entire model

Redis supports strings, hashes, sets, sorted sets, streams, and other structures. It is more than a string dictionary. See Redis data types.

A product display cache can use a clear key and expiration:

SET product:12:summary '{"id":12,"name":"Stand","priceCents":4000}' EX 60
GET product:12:summary
DEL product:12:summary

EX 60 specifies a lifetime in seconds. See the Redis SET command.

Cache-aside reads check the cache, load the database on a miss, and populate the cache. Updates commonly commit the database before invalidating the cache, but a concurrent old read can still refill stale data. TTL bounds some stale periods; it does not automatically provide strong consistency.

Do not make inventory decisions from a display cache. Consider simultaneous misses on a hot key, repeated requests for nonexistent IDs, and whether the database can absorb traffic during a cache outage. Redis durability and eviction depend on configuration; a cache should not casually become the only source of order facts.

6.3 Wide-column databases: design keys around partition-local queries

Cassandra modeling commonly starts from queries. For “one user's orders in a month,” a design might partition by (user_id, month) and organize rows by time and order ID within the partition:

partition key:  (user_id, month)
clustering key: (created_at DESC, order_id DESC)
query:          one user's orders within one month

This sketches an access model, not universal DDL. User-based and product-based queries may need different tables. Redundancy supports known access paths and brings maintenance responsibilities. See Cassandra data modeling.

Partition keys distribute data. Hot users and unbounded partitions can cause problems. Adding a month can limit some growth but makes cross-month queries visit multiple partitions. Distribution alone does not guarantee fast writes.

6.4 Graph databases: query relationships directly

A graph can represent users, products, follows, and purchases. This Cypher example asks for products bought by people the current user follows; it does not run against the earlier SQL schema:

MATCH (me:User {id: 'user_1'})-[:FOLLOWS]->(friend:User)
      -[:BOUGHT]->(product:Product)
RETURN DISTINCT product.id, product.name;

Nodes, relationships, and properties make path-oriented queries direct. See Neo4j graph concepts.

Having user relationships does not itself require a graph database. Evaluate traversal depth, relationship volume, and latency targets; simple joins may be sufficient. Recommendation systems can also require ranking models, content features, and other algorithms beyond traversal.

6.5 Wide-column and analytical columnar storage are different

Wide-column modeling emphasizes partitions, keys, and access patterns. Analytical columnar storage organizes values by column to support compression and scanning a few fields across many records.

Creating orders is a small-scope transactional workload. Sales analysis may aggregate months of records. An analytical system such as ClickHouse targets the latter; the shared word “column” does not make it the same category as Cassandra. See the ClickHouse introduction.

Time-series workloads often need time-oriented ingestion, retention, downsampling, and windowed aggregation. These are workload and modeling requirements; the relevant systems may also support SQL. Not every specialized database is accurately described as “non-SQL.”

6.6 Consistency and scale cannot be inferred from a SQL label

Relational databases can replicate and partition, and some products offer distributed transactions. NoSQL products can provide transactions and multiple consistency choices. Guarantees depend on the product, topology, configuration, and operation.

During a network partition, define which requests fail or what stale or conflicting state is allowed. CAP is not three freely selectable feature switches. More actionable questions are whether an order confirmation may read old data, what failure looks like to the user, and how recovery reconciles discrepancies.

7. MySQL, PostgreSQL, and system selection

7.1 Avoid ranking products as simple versus advanced

MySQL InnoDB provides transactions and ACID-related behavior. It is misleading to limit MySQL to simple reads and writes or describe consistency as exclusive to PostgreSQL. See MySQL InnoDB and ACID.

PostgreSQL's JSONB, indexing, and extension ecosystem deserve evaluation against concrete requirements, but JSON columns should not replace all relational modeling. See PostgreSQL JSON types.

Either database can support this order system. Required capabilities, measurements, and operational costs are better selection criteria than labels such as “MySQL is lightweight” or “PostgreSQL is strict.”

7.2 Meet the main requirement before adding stores

For this small system, a relational database can initially hold users, orders, lines, and inventory. Evaluate caching, search, or analytical stores when evidence supports their additional role.

If a primary write succeeds but a search update fails, the change must not silently disappear. Reliable delivery, event IDs or versions for deduplication, and rebuilding procedures are needed. Every additional database adds a failure boundary as well as a query capability.

7.3 Replicas and sharding must answer business questions

Read replicas can offload reads, but replication lag may hide a newly placed order from its buyer. Critical reads may need the primary, session stickiness, or another read-after-write strategy.

Sharding can distribute capacity and traffic while complicating cross-shard joins, uniqueness, transactions, and rebalancing. Identify whether the real bottleneck is a query, connections, storage, or single-node capacity before sharding for hypothetical future growth.

8. Operations are part of database design

8.1 Slow queries and connection pools

A slow request may be waiting for a connection, waiting for a lock, executing SQL, or transferring too much data. Observe tail latency, pool waits, slow queries, lock waits, and storage pressure before choosing a fix.

A larger pool is not always better. Many application replicas each opening many connections can increase contention. Short transactions, bounded background batches, and sensible timeouts can help more than simply increasing the connection count.

8.2 Migrate through compatible intermediate states

To introduce a required field, first consider a nullable column, compatible old and new application versions, a batched backfill, validation, and only then stricter constraints and removal of old logic. Old instances should not immediately lose the ability to write.

Index creation, type changes, and new constraints on large tables can introduce locks and load. Online options have version-specific limits and recovery requirements. Evaluate migrations at realistic scale, not just against an empty table.

8.3 Replicas are not backups

A replica can quickly reproduce an accidental deletion and therefore does not independently provide a historical recovery point. Define acceptable data loss through RPO and restoration time through RTO, then perform actual restore drills. See PostgreSQL backup and restore.

PostgreSQL point-in-time recovery, for example, needs a matching base backup and the required WAL, not a few disconnected log files. Validate data, constraints, permissions, application connectivity, and elapsed recovery time rather than merely checking that backup files exist.

8.4 Retention and archiving

Historical orders, audit logs, and caches have different lifecycles. Define retention, archival queries, and deletion procedures early to reduce the impact of large cleanup jobs on locks, indexes, and storage.

Partitioning can help some time-range management and cleanup tasks but does not accelerate every query. Archiving must still account for relationships to products and users and for privacy requirements; it should not create a gap in data integrity.

9. Practical checks and self-assessment

Use the dataset to verify these results, keeping concurrency experiments in an isolated database:

• Order 101 totals 200 and remains 200 after changing the current product price.
• Paid-order counts for the three users are 1, 2, and 0.
• Incorrect aggregations return 500 and 250; the correct paid total is 300.
• UTC daily paid sales are 200 and 100.
• Latest orders are 102 and 104, which differs from latest paid orders.
• Invalid foreign keys, duplicate lines, negative stock, and missing required payment times fail constraints.
• Rollback removes the new order and restores inventory.
• Two connections competing for the last item cannot both create an order successfully.
• A version conflict triggers a new business decision, not an unconditional overwrite.
• Duplicate requests, cache invalidation failures, and backup restoration have explicit procedures.

Try answering these questions rather than only reciting definitions:

1. Why not compute historical orders from current prices? An offer today and a sale snapshot describe facts at different times.
2. Why does SUM(DISTINCT amount) not repair every duplicate JOIN? Different orders may share an amount, so value deduplication removes real transactions.
3. Does adding a surrogate primary key normalize a table? No. Real functional dependencies and other candidate keys still need analysis.
4. Why might an indexed table use Seq Scan? A small table or broad read may favor it; unsuitable conditions or estimate errors also require plan inspection.
5. Why can nonnegative stock still permit overselling? Two callers can both replace their old value of one with zero, missing a decrement.
6. What do transactions and idempotency keys each solve? Transactions define local commit and rollback, while idempotency identifies repeated requests.
7. Why model carefully if MongoDB supports transactions? Transactions do not eliminate cross-document coordination, document growth, or access-path costs.
8. How do you demonstrate recoverability? Restore the required point into an isolated environment, verify data and application behavior, and measure recovery time.

The aim is to derive a schema, queries, constraints, and concurrency policy from an operation, then challenge that design with results, execution plans, and failure experiments.