E2 · Publication Volume 24

Database versus File Storage

queryability, immutability, large objects, caching and archives

Learning objectives

  • Explain why queryability, immutability, large objects, caching and archives require explicit semantic modelling.
  • Design identities, relations and constraints that preserve database versus file storage across exchange.
  • Separate hard release gates from diagnostic metrics and interpretation choices.
  • Produce a storage responsibility map with reproducible derivations and tested recovery from synthetic evidence.

The lesson is complete only when the learner can defend both the model and the release decision. A neat schema without evidence, tests or declared limitations is an unverified design. The assessed artefact must make assumptions visible and distinguish source assertions from derived conclusions.

Decision context

Place data according to mutation pattern, query shape, size, consistency, sharing and retention. Entity registries and current relationships often benefit from a database; raw captures, model geometry and released analytical snapshots often benefit from immutable objects. Convenience alone is not an architectural criterion.

Start with a decision record: name the intended use, the evidence required, the consequence of error, the accepted uncertainty and the role authorised to accept residual risk. Then ask whether the proposed model can answer the decision question without relying on filename conventions, row order, undocumented defaults or someone’s memory. This prevents technology selection from concealing a missing semantic requirement.

The same record may be fit for one use and unfit for another. A rapid exploratory view can tolerate conditions that a released exchange package cannot. Fitness is therefore stated against a use, contract version and quality gate rather than attached permanently to the data.

Core concept

Databases and files solve different storage problems. A database supports constrained updates, indexed queries and concurrent transactions; immutable files support portable exchange, reproducible snapshots and economical large-object storage. A sound architecture combines them through explicit system-of-record and derivation boundaries.

The working scope is queryability, immutability, large objects, caching and archives. For each item in that scope, distinguish the thing itself, the label used by a source, the claim made about it and the record that carries the claim. Identity is not a display name; a value is not its unit; an observation is not a model; current is not the same as valid. These distinctions create explicit places for correction, uncertainty and competing interpretations.

A good semantic design can be explained as a set of sentences before it is encoded. Each sentence identifies a subject, a property or relationship, an object or result, and the context under which the claim holds. Physical tables and files are then projections of those sentences, not their source of meaning.

Semantic model

Define four roles: authoritative mutable records, immutable source objects, derived query stores and caches. Every derived store points to source versions and transformation manifests. Caches are disposable and never the only copy. Archives retain content, metadata, schema, vocabulary and fixity evidence as one preservation package.

Test every proposed record against seven questions: What has identity? What type is it? Which property or relationship is asserted? Which spatial and temporal context applies? Which state or qualifier modifies the assertion? Which evidence supports it? Which version and activity produced the stored representation? Missing answers become explicit contract gaps.

Normalisation is used to separate independent facts, not to maximise the number of tables. A compact nested object can be semantically sound if the same identities, constraints and provenance remain explicit. Conversely, a highly normalised database can still be ambiguous when relationships and units exist only in documentation or application code.

Constraints and invariants

| Invariant | Executable or review test | | --- | --- | | One system of record per assertion | Document authority and conflict resolution for every data class. | | Released objects are immutable | Corrections create new versions and supersession links. | | Derived stores are reproducible | Record exact sources, code, parameters and validation. | | Caches are non-authoritative | Loss or eviction cannot destroy unique evidence. |

An invariant is a condition that must remain true across storage, export, correction and reprocessing. Implement it as close to the authoritative boundary as practical and repeat the check at exchange boundaries. Record rule identifier, version, severity, evaluated scope, observed value and outcome so a failure can be reproduced.

Hard gates protect identity, semantic validity, required provenance and authorised use. Diagnostic checks reveal unusual values or patterns but require interpretation. Never convert a diagnostic threshold into deletion or correction without a reviewed rule and preserved source evidence.

Quantitative reasoning

Characterise workloads with rows or objects scanned, selected columns, spatial windows, update frequency and required consistency. Measure cache hit ratio H = n_h / n_q and reproduction success rate, but interpret both with freshness. A fast cache serving an obsolete contract version is a failure, not a performance success.

Every reported ratio states its numerator, denominator, exclusions and evaluation time. Stratify results by source, entity type, contract version or processing run where aggregation could hide a local failure. Counts accompany percentages so a seemingly large change based on a tiny denominator remains visible.

Precision is part of meaning. Do not add decimal places merely because a storage type permits them, and do not round identity, interval or coordinate fields without a declared tolerance and test. Quantitative summaries support a release decision; they do not replace semantic review.

Evidence and uncertainty

Storage evidence includes transaction logs or version history, object fingerprints, backup tests, restoration tests and dependency manifests. Durability claims require tested recovery. Replication without versioned semantics can faithfully preserve an uninterpretable dataset.

Build an evidence packet containing preserved source reference, acquisition or assertion context, applicable method, validation results, reviewer decision and links to derivatives. Classify uncertainty as observational, semantic, structural, parametric or policy-related where that distinction changes treatment. “Unknown” is a valid state when the evidence cannot justify a stronger claim.

Contradictory evidence remains available. The model may select one current assertion, but the reason, competing assertion and effective time are retained. This makes later reinterpretation possible without pretending the earlier evidence never existed.

Interfaces and storage

Expose stable logical contracts rather than storage internals. Query services return contract versions and pagination semantics; object manifests return immutable locations, media types, sizes and checksums. A consumer should not need to know which cache or physical partition served the response.

Design an interface from the logical contract outward. Specify identifiers, types, cardinalities, units, value states, coordinate and time references, version negotiation, validation behaviour and structured errors before choosing a serialisation. The physical representation then declares its mapping to those logical elements.

Storage optimisation may partition, compress, index or cache data, but it must not change identity or silently remove context. A derived representation points to immutable inputs and a processing manifest. A cache carries freshness and contract-version information and is never treated as the only evidence copy.

Governance and access

Retention, deletion, legal hold, access and preservation policies apply by data class and version. Lifecycle actions are logged. A migration proves record counts, key equality, constraints, checksums and representative queries before authority moves to the new store.

Governance is expressed through named roles, review states and versioned decisions, not through references to a particular organisation. Define who may propose, validate, approve, supersede and withdraw each governed resource. The audit trail records the role and event while avoiding unnecessary personal data.

Apply least-necessary access to source evidence and derivatives. Access controls must not erase identifiers, lineage or quality metadata needed to understand an authorised release. When policy is unresolved, quarantine the output with a precise reason and escalation route.

Integration checkpoint

Authoritative records, immutable objects, query stores and caches
Authoritative records, immutable objects, query stores and caches

The diagram summarises the control flow for this lesson. Read it from source evidence through semantic structure and validation to a decision-ready artefact. Each arrow should correspond to a declared relationship or transformation; each boundary should have a contract; each released node should have an identity, version and provenance pointer.

Integrate the lesson by adding a storage responsibility map with reproducible derivations and tested recovery to the evolving synthetic data package. Verify that earlier artefacts still resolve and that the new model does not overwrite observations, identifiers, values or versions introduced in previous lessons. Record every changed assumption.

Synthetic worked example

A synthetic architecture keeps entity identities and current status in a relational store, raw instrument files and model meshes as immutable objects, a columnar snapshot for analysis and a disposable map cache. The learner traces one displayed value back through cache, snapshot and transformation to the original object and registry record.

Work the example in four passes:

  1. Preserve the received records and write the intended decision without correcting anything.
  2. Identify entities, claims, context, uncertainties and policy constraints; mark every unresolved item.
  3. Apply the versioned rules, create derivatives and record the exact transformation plus validation evidence.
  4. Issue an accept, reject or quarantine decision and show how an independent reviewer can reproduce it.

Because the example is entirely synthetic, its values demonstrate method only. The important result is the chain from received evidence to justified decision. If a required fact is absent, the worked solution records the gap rather than manufacturing a plausible value.

Practice task

Classify twelve synthetic data products by authority, mutability, query pattern, object size, retention and reproducibility. Place each into a storage role, draw derivation links, define cache invalidation and demonstrate a restore plus a rebuild from immutable inputs.

Use the following acceptance criteria:

  • All required identifiers and references resolve to declared types.
  • Every transformation preserves the received evidence and records its derivation.
  • Invalid, unknown and inapplicable states remain distinct and machine-testable.
  • The output identifies the contract, vocabulary and processing versions used.
  • A second reader can reproduce the validation result without private knowledge.

Submit the source snapshot, authored contract or model, validation output, derivative, manifest and a short decision record. A screenshot alone is insufficient because it cannot demonstrate the exact input, version or rule execution.

Common failure modes

  • A shared folder becomes an undocumented system of record.
  • A cache is the only copy of a derived product.
  • Released objects are overwritten in place.
  • A backup exists but restoration and semantic dependencies are untested.

These failures share a pattern: convenient representation is mistaken for verified meaning. Diagnose the earliest boundary at which an assumption became implicit. Correct by restoring source evidence, making the assumption a versioned field or rule, rerunning dependent transformations and superseding—not overwriting—the affected release.

Do not repair a failure by adding an undocumented default. A blocked result with a specific missing dependency is safer and more reusable than a complete-looking result whose meaning cannot be reconstructed.

Review questions

  1. Which storage role owns an assertion?
  2. Why does immutability improve reproducibility?
  3. What makes a cache safe to discard?
  4. Which evidence must precede a system-of-record migration?

For each answer, identify the governing invariant, the evidence needed to evaluate it and the appropriate release behaviour when the invariant fails. A strong answer distinguishes scientific uncertainty from missing semantics and distinguishes a recoverable warning from a hard contract violation.

Sources and further reading