Skip to main content

Applying Database Testing: AtlasShop Database Validation

Prerequisites: You should already have completed Applying Database Testing: AtlasBank Loan Disbursement Validation. Leads to: After this, you'll be ready for Common Mistakes in Database Testing.

Every AtlasBank example in this path so far has used the same, by-now-familiar banking schema. This module deliberately steps outside it: AtlasShop, a fictional e-commerce platform with its own schema — Products, Inventory, Orders, OrderItems, Payments — practicing this path's full toolkit against a genuinely unfamiliar data model, the way a real database tester has to when they move to a new team or a new domain.

The Feature: AtlasShop Checkout and Inventory Reservation​

Verifying AtlasShop's checkout flow: a customer adds items to a cart, checks out, Inventory is decremented to reserve the purchased stock, an Orders row is created, and a Payments row records the transaction — a multi-table operation with the same shape this path has tested throughout, on tables none of AtlasBank's examples ever used.

Foundations and Validation (Sections 1–2)​

Applying SQL for Testers's JOIN pattern to a new relationship: Orders joined to OrderItems and Products confirms every ordered item's price at time of purchase matches what was actually charged in Payments — a reconciliation check structurally identical to AtlasBank's fund-transfer examples, now applied to Products.price and Payments.amount instead of Transactions. Applying Constraints, Keys, and Relationships's boundary testing to Inventory.quantity_available: a CHECK (quantity_available >= 0) constraint should reject any operation that would take stock negative.

A real defect surfaces here: the constraint exists and correctly rejects a single order attempting to purchase more units than available — but a boundary test ordering exactly the last available unit succeeds correctly, while a follow-up test immediately re-querying Inventory for that product shows quantity_available still at 1, not 0 — the decrement itself happens in application code after the constraint check, not atomically with it, a gap invisible to a single-order test and only found by checking the actual post-order inventory state, not just whether the order succeeded.

Advanced Mechanics (Section 3)​

Applying Transactions, Locks, and Concurrency to the exact gap the previous check hinted at: two customers are made to check out the same product, with exactly one unit in stock, at nearly the same instant.

A second real defect surfaces here: both checkouts succeed. This is a lost update with a genuinely different flavor than any prior AtlasBank example in this path — two reads of quantity_available = 1 both pass the "is stock available" check before either write decrements it, and both orders are confirmed, overselling a single physical unit to two different customers. The fix (verified by re-running the same concurrent test) requires the stock check and decrement to happen as one atomic operation, not two separate steps — directly connecting back to this module's own first defect, which had already hinted at the same non-atomic gap.

Performance and Compliance (Section 4)​

Applying Database Performance Testing's N+1 detection to an order-history page listing a customer's past orders alongside each order's items: query-count testing at 3 orders versus 30 orders reveals the same 1-query-per-row pattern this path's AtlasBank beneficiary-statement example found, now in OrderItems instead of Beneficiaries — confirming the pattern is a general one, not specific to any single schema. Applying Database Security Testing's least-privilege check to AtlasShop's read-only sales-analytics tool: its granted privileges are compared against its documented, read-only need.

A third real defect surfaces here: the analytics tool's database credential has full write access to Payments — a table it has no legitimate reason to ever modify — the exact least-privilege gap shape this path's own Database Security Testing module described, now confirmed in an entirely different system than any prior example used.

What This Application Module Shows​

Practicing this path's toolkit against AtlasShop confirms these techniques generalize — every defect class this module found (a non-atomic constraint check, a lost update, an N+1 pattern, a least-privilege gap) is one this path already taught against AtlasBank, and each transferred directly to an unfamiliar schema without needing new technique. That transfer is deliberate: the value of practicing against a second, contrasting domain isn't learning something new, it's confirming the skill itself — not just familiarity with one schema — is what was actually learned.

Common Mistakes​

Mistake 1: Assuming a passing boundary test (the order succeeded) also confirms the resulting state is correct. The inventory defect specifically required checking the post-order state (quantity_available), not just whether the boundary-value order itself succeeded or failed.

Mistake 2: Testing a constraint and a concurrency scenario as unrelated checks instead of recognizing when one hints at the other. This module's own first defect (a non-atomic check-then-decrement) directly predicted the second (a lost update under concurrency) — noticing the connection made the second test case obvious rather than a separate discovery.

Mistake 3: Assuming techniques learned against one schema won't transfer to an unfamiliar one. Every defect class in this module was already taught against AtlasBank — the skill, not schema-specific familiarity, is what this path has actually been building.

Best Practices​

Practice 1: When practicing against a new, unfamiliar schema, apply the same systematic toolkit rather than starting from scratch. Every technique from Sections 1–4 transferred directly to AtlasShop's schema in this module — a systematic approach doesn't depend on prior familiarity with the specific tables involved.

Practice 2: Verify actual post-operation state, not just whether an operation succeeded or failed. This module's inventory defect specifically required checking quantity_available after a successful order, not just confirming the order itself went through correctly.

Practice 3: Treat a defect in one dimension as a hint to test an adjacent one, especially when the underlying cause (like a non-atomic operation) plausibly affects both. This module's constraint and concurrency defects turned out to share one root cause — recognizing that connection made the investigation faster and more thorough.

Key Takeaways​

  • This path's entire toolkit — SQL verification, constraint boundaries, concurrency testing, N+1 detection, least-privilege checks — transferred directly to AtlasShop's unfamiliar schema, confirming the underlying skill generalizes beyond AtlasBank specifically.
  • A boundary test that succeeds doesn't automatically confirm the resulting state is correct — check post-operation state directly, not just success or failure.
  • A non-atomic check-then-write pattern (verified stock, then separately decremented it) is a root cause that can produce both a constraint-adjacent gap and a concurrency defect — the same underlying issue, two different symptoms.

What You Just Learned​

  • How this path's full toolkit applies to a genuinely new, unfamiliar schema without requiring new technique
  • Why checking post-operation state (not just success/failure) is necessary to catch certain constraint-adjacent defects
  • How a non-atomic stock check-and-decrement produced both a state-consistency gap and a genuine overselling lost-update defect
  • How the same N+1 and least-privilege patterns from AtlasBank examples confirmed as general, schema-independent defect classes

Next: Common Mistakes in Database Testing

Interview Questions​

Q1: How would you approach testing a database you'd never seen before, on a new team or project?

What to look for: A candidate who describes applying a systematic, general toolkit (constraint testing, concurrency testing, relationship verification) rather than describing techniques that only make sense for a schema they already know well — showing the skill, not just familiarity, transfers.

Q2: Why might an operation that correctly rejects an invalid boundary case still have a real underlying defect?

What to look for: A candidate who explains that a check succeeding or correctly rejecting doesn't guarantee the resulting state is fully correct — citing an example like this module's inventory case, where the check itself worked but wasn't atomic with the corresponding write.

Glossary​

No new terms are introduced in this module — every concept used above is defined in its linked source module.

Quick Revision​

Remember these five points:

✓ This path's toolkit generalizes — every technique transferred directly to AtlasShop's unfamiliar schema without new technique.

✓ Verify post-operation state directly, not just whether an operation succeeded or failed.

✓ A non-atomic check-then-write pattern can produce both a state-consistency gap and a genuine concurrency defect.

✓ N+1 query patterns and least-privilege gaps are schema-independent defect classes, not specific to any one domain.

✓ Practicing against an unfamiliar schema confirms the underlying skill, not just familiarity with one system.