Q47 proof · Complex database architecture: ER modelling, indexing, partitioning, replication, sharding, tuning

47. Complex database architectures

Answer as submitted

“Yes — database architecture and tuning are long-standing strengths across relational and NoSQL platforms.”

Technical decision: expand. The submitted line lists the areas. This page is the separate, detailed version, and the thread through all of it is a single habit: every decision is taken from evidence — the query inventory, the execution plan, the measured load, the timed drill — rather than from intuition. An index nobody measured and a failover nobody executed are both beliefs, and this page is about not holding them.

Expanded answer

Yes, across Oracle, SQL Server, MySQL and PostgreSQL as well as MongoDB, DynamoDB, DocumentDB, Neptune and Cassandra/Keyspaces. ER modelling is done in three passes with three audiences: conceptual with the business, logical normalised to third normal form with every denormalisation written down beside the read it serves, and physical with the types, the partitioning and the index set. History is a design decision rather than an afterthought — audited tables get temporal or append-only history, because reconstructing what a row looked like in March is impossible if nobody kept the log — and all migrations are forward-only and versioned. Keys and constraints: a surrogate primary key so references stay stable, plus the natural unique key that makes a duplicate impossible rather than unlikely, foreign keys and check constraints declared rather than implied by the application, and rowversion on every table an application updates. Indexing is derived from an inventory of the queries that actually run, in their real shapes and frequencies: composite columns in equality-then-range order, INCLUDE so a read is answered from the index alone, filtered indexes for the sparse predicate, fragmentation and statistics maintained on a measured schedule — and an index with no reader removed, because it is a write cost with nothing on the other side. Partitioning is used for manageability first: range by date, list by tenant, hash for an even spread, with partition switching so archiving a 40 GB month is a metadata operation instead of a delete that locks and bloats, and with the queries designed to follow the scheme so the partition elimination actually happens. Replication follows the recovery objective — what the business can lose and how long it can be down decides between asynchronous streaming, synchronous commit and log shipping — and is then proven by drills: failover, point-in-time restore and archive restore, all timed and recorded. Sharding is a key-design decision with the access pattern in front of it, in relational engines or expressed directly in a NoSQL key design (DynamoDB partition and sort keys, Cassandra partition keys) that achieves the same distribution without a routing layer to rebalance. Performance optimisation is a discipline with evidence at each step: triage from the plan cache ordered by reads, one query at a time, measured before and after, with the write cost of every index included in the arithmetic.

Area	The decision	The evidence it rests on
ER modelling	3NF with denormalisation justified per read; temporal or append-only history for audited tables	the documented list of denormalisations and the query each one serves
Keys and constraints	surrogate key plus natural unique key; constraints in the schema, not only in code	a duplicate or an impossible row is refused by the engine, not by an application check
Indexing	derived from the query inventory; covering, filtered, equality-then-range	the execution plan before and after, plus the read/write ratio per index
Partitioning	range by date, list by tenant, hash for spread; switching for archive	archive time in seconds and no lock on the live partition
Replication	topology chosen from RPO and RTO, synchronous commit where a lost write is unacceptable	quarterly failover and monthly PITR drills, timed and recorded
Sharding	shard or partition key chosen with the access pattern and the cross-shard cost written down	zero cross-shard reads on the hot path; a documented rebalancing plan
Performance	triage from the plan cache by logical reads; one change at a time, measured	before-and-after plans attached to every index migration

Live database-architecture workflow proof

The running component below changes its area/result view through real event binding. Select an area to see the decision, the reasoning behind it, and the evidence that holds it up.

DATABASE ARCHITECTURE WORKFLOWRUNNING

Choose an area of the database architecture

Select an area to inspect the decision, the reasoning and the evidence.

$ conceptual → logical → physical · 3NF with justified denormalisation · temporal history for audited tables · versioned, forward-only migrations

The model is built in three passes, each for a different audience: the conceptual one with the business, the logical one normalised to third normal form with every denormalisation written down beside the read it serves, and the physical one with types, partitioning and the index set. History is a design decision rather than an afterthought, because reconstructing what a row looked like in March is impossible if nobody kept the log.

denormalisations applied: 3, each with the query it serves
rows that can dangle because only the application checks: 0
history reconstructable for the audited tables: all of it
migrations: forward-only, versioned, applied by CI
destructive migration shipped without a backfill plan: 0

What the design discipline buys

Rules I apply. Model before you type, and normalise first because denormalising a normalised schema is a decision while normalising a denormalised one is a project; give every business table both a stable surrogate key and the natural unique key, since that is what makes a retried create safe rather than lucky; leave the invariants the engine can enforce to the engine, because a check constraint survives two concurrent requests and an application check does not; derive the index set from an inventory of real queries and delete the ones with no reader; partition for manageability so archiving is a switch rather than a delete; choose the topology from the recovery objective and then execute the failover on a schedule, since an untested failover is a plan and a plan is not an RTO; pick the shard or partition key with the access pattern in front of you and write down the cross-shard cost before committing; and change one thing at a time with the plan captured before and after, because a tuning change that was not measured is a change nobody can attribute a result to.

Boundary for Q47: this page covers database architecture and design — the model, the keys, the indexes, the partitions and the topology. The scaling levers specifically, meaning read replicas for scale-out, sharding as a capacity response and caching layers, are the subject of the Q48 question; the query-level tuning techniques (sargability, parameter sniffing, statistics) are on the Q09 proof page; and the NoSQL access-pattern design is on Q10.

Source files

Select a file to inspect the proof with syntax highlighting and line numbers. The first file is the code running the component above; the others are the physical model as DDL with every index naming the query it serves, the topology with its recovery objectives and drills, and the execution-plan assertions that keep the model honest.

Acceptance criteria

Addresses all six areas the question names, each with a decision behind it.
Derives the index set from a query inventory and removes the indexes with no reader.
Shows an interactive proof of choosing an area and reading the evidence.
Chooses the topology from a stated recovery objective and proves it with a timed drill.
Validates every performance change with an execution plan captured before and after.