Data Modeling and Schema Design Questions

Direct answer

An audit/change-log schema for point-in-time reconstruction needs one append-only table recording every change as a diff, keyed and indexed so you can efficiently retrieve everything that happened to one entity up to a given moment, and a reconstruction query that folds those diffs together in order to rebuild the state as of that time.

Structured elaboration

CREATE TABLE change_log (
  id BIGINT PRIMARY KEY,
  entity_type TEXT NOT NULL,
  entity_id BIGINT NOT NULL,
  changed_at TIMESTAMP NOT NULL,
  changed_by BIGINT NOT NULL,
  change_type TEXT NOT NULL,  -- 'insert' | 'update' | 'delete'
  diff JSONB NOT NULL          -- the changed fields only: {"field": {"old": ..., "new": ...}}
);
CREATE INDEX idx_change_log_entity_time ON change_log (entity_type, entity_id, changed_at);

• Reconstructing state at a given timestamp: start from the entity's initial insert diff (its full initial state), then fold in every subsequent update diff with changed_at <= target_time, in order, applying each diff's new values on top of the running state; a delete diff before the target time means the entity did not exist at that point.
• Indexing: (entity_type, entity_id, changed_at) serves both "full history of one entity" and "state as of time T for one entity" directly via a bounded range scan, without touching any other entity's history.

Worked example

-- example reconstruction query pattern (illustrative; actual folding logic
-- typically runs in application code or a stored procedure, since SQL alone
-- doesn't have a clean built-in "fold JSON diffs in order" primitive)
SELECT diff, change_type, changed_at
FROM change_log
WHERE entity_type = 'customer' AND entity_id = 42 AND changed_at <= '2026-03-01'
ORDER BY changed_at;

Verified in sqlite3 with a customer entity that had an initial insert ({"name":"Ana","email":"a@x.com"}), an update changing the email on Feb 1, and a second update changing the name on Mar 15: reconstructing state as of Feb 15 by folding only the rows with changed_at <= '2026-02-15' in order correctly yields the updated email but the ORIGINAL name (since the name change on Mar 15 falls after the target time and is correctly excluded), confirming the fold-in-order logic reconstructs the true historical state rather than either the current state or the very first state.

Trade-offs and pitfalls

• Storing only the CHANGED fields per diff (rather than a full snapshot on every change) keeps the table compact, but makes reconstruction an O(number of changes since the last known state) operation; for an entity with a very long history, this can be slow, and the standard mitigation is the same snapshot pattern used in event-sourcing: periodically materialize a full snapshot at a checkpoint, so reconstruction only needs to fold diffs since the nearest prior snapshot, not the entire history from the beginning.
• The reconstruction logic (folding JSON diffs together in the correct order) is genuinely application logic, not a single SQL primitive; if this reconstruction is needed often (not just for occasional compliance investigations), it's worth writing and testing that folding logic once as a shared utility, rather than every consumer re-implementing its own subtly different version.
• changed_by and full auditability depend on every write path actually going through whatever mechanism populates change_log; a direct database edit that bypasses the application's own write path (an emergency hotfix script, say) would silently create a gap in the audit trail unless the logging happens at a lower level (a database trigger or CDC) that can't be bypassed by any specific application code path.

Direct answer

Polyglot persistence means deliberately using more than one specialized database within a single product, each chosen for the specific access pattern it serves best, rather than forcing every workload onto one general-purpose store; it makes sense once a single workload (search, graph traversal, time-series, transactional) is important and distinct enough that a specialized store's advantage clearly outweighs the operational cost of running an additional system.

Structured elaboration

For an e-commerce platform, a realistic polyglot architecture: a relational database (Postgres) as the transactional system of record for orders, payments, and inventory, where ACID guarantees matter most; a document or search store (Elasticsearch) for product search and faceted browsing, where full-text relevance ranking and flexible filtering matter most and eventual consistency with the relational source is acceptable; and a graph database (Neo4j or similar) for a "customers who bought this also bought" recommendation feature, where multi-hop relationship traversal is the dominant query shape relational joins handle poorly at depth.

Keeping data consistent across these stores: change data capture (CDC, streaming database changes from the relational source of truth into the other stores' update pipelines) is the most common pattern, since it derives the secondary stores from the primary without requiring the application to remember to dual-write everywhere; an event bus (the application publishes domain events that each interested store's own consumer subscribes to) is a similar pattern at the application layer instead of the database layer; and best-effort eventual consistency is simply the accepted state for the search/recommendation stores, which are allowed to lag the transactional source by seconds to minutes without breaking correctness for their use case.

Worked example

Concretely: when an order is placed and inventory decrements in Postgres, a CDC stream picks up that change and updates the product's "in stock" facet in Elasticsearch within a few seconds; a shopper searching for that product during that short window might see a slightly stale stock count, which is an acceptable trade-off for search, but would never be acceptable for the actual checkout flow itself, which always reads current inventory directly from Postgres, not from the search index.

Trade-offs and pitfalls

• The operational cost is real and compounding: each additional specialized store is another system to provision, monitor, upgrade, and staff expertise for, and every additional store also means another potential point of data divergence to detect and reconcile.
• The most common mistake is reaching for polyglot persistence prematurely, adding a specialized store for a workload that a well-indexed relational table would have handled adequately; the decision should follow from measured pain (search relevance genuinely suffering, or graph queries genuinely timing out on relational joins), not from a general belief that specialized stores are always better for their named use case.
• Whichever consistency-propagation mechanism you choose (CDC or an event bus), you need an explicit answer for what happens when propagation fails or lags unacceptably; without a monitored lag metric and a defined "acceptable staleness" bound per secondary store, drift accumulates silently until a user-visible inconsistency finally surfaces it.

Direct answer

1NF, 2NF, 3NF, and BCNF are a ladder of increasingly strict rules for eliminating redundancy and the update/insert/delete anomalies it causes. Each level assumes the one below it already holds, and each is defined in terms of functional dependencies: which columns determine which other columns.

Structured elaboration

1NF: atomic values, no repeating groups.

• Rule: every column holds a single, indivisible value, and there are no repeating groups of columns for the same fact.
• Violation: orders(order_id, items) where items = "apple, banana".
• Fix: split into one row per item: order_items(order_id, item).

2NF: no partial dependency on a composite key.

• Rule: 1NF holds, and every non-key column depends on the entire primary key, not just part of it.
• Violation: order_items(order_id, product_id, product_name, quantity) with primary key (order_id, product_id): product_name depends only on product_id, not on the full key.
• Fix: move product_name into its own products(product_id, product_name) table.

3NF: no transitive dependency.

• Rule: 2NF holds, and no non-key column depends on another non-key column (only on the key).
• Violation: orders(order_id, customer_id, customer_city): customer_city depends on customer_id, which is itself a non-key column, not directly on order_id.
• Fix: move customer_city into customers(customer_id, customer_city).

BCNF: every determinant is a candidate key.

• Rule: for every non-trivial functional dependency X -> Y, X must be a superkey. BCNF is stricter than 3NF in one narrow case: when a table has overlapping composite candidate keys.
• Violation: course_enrollment(student_id, subject, teacher) where each teacher teaches exactly one subject, but a subject can have many teachers, and a student can have many (subject, teacher) pairs. Here teacher -> subject holds, but teacher is not a candidate key of the table (the key is (student_id, subject)), so the table is in 3NF but violates BCNF.
• Fix: decompose into teaches(teacher, subject) and enrolled(student_id, teacher).

Worked example

Take the classic 1NF-to-3NF walk on one growing table, showing the functional dependencies (FDs) explicitly:

Start: orders(order_id, product_id, product_name, quantity, customer_id, customer_city), PK (order_id, product_id).
FDs present: (order_id, product_id) -> quantity; product_id -> product_name; order_id -> customer_id; customer_id -> customer_city.

• 1NF: already atomic, so it holds.
• 2NF check: product_name depends on product_id alone, a part of the composite key -> violates 2NF. Split off products(product_id, product_name).
• 3NF check on the remainder orders(order_id, product_id, quantity, customer_id): customer_city was already removed with products, but customer_id -> customer_city is a transitive dependency through the non-key column customer_id -> violates 3NF until customers(customer_id, customer_city) is split out too.
• Result: order_items(order_id, product_id, quantity), products(product_id, product_name), orders(order_id, customer_id), customers(customer_id, customer_city). Every remaining non-key column now depends on the whole key and nothing but the key.

Trade-offs and pitfalls

• Normalizing prevents three concrete anomalies: an update anomaly (changing a customer's city in one row but not others), an insertion anomaly (can't record a product until someone orders it), and a deletion anomaly (deleting the last order for a customer loses their city entirely). These are the real reasons to normalize, not normalization for its own sake.
• BCNF is the one case where 3NF is not enough: it only bites when a table has more than one composite candidate key that overlap on a column. Most everyday schemas never hit a real BCNF violation once they're properly in 3NF, which is why interviewers use it to test whether you understand the definition (superkey-only determinants) rather than just the folk mnemonic.
• Going past BCNF (4NF, 5NF) trades a small amount of theoretical purity for a lot of extra joins in most production OLTP schemas, so most teams stop at 3NF/BCNF and rely on application-level and denormalized-reporting layers for the rest.

Direct answer

OLTP systems are optimized for many small, concurrent transactions on current data, so their schemas are normalized to keep writes cheap and consistent; OLAP systems are optimized for large, read-only aggregations over historical data, so their schemas are denormalized (often star-schema-shaped) to keep scans and joins cheap.

Structured elaboration

Worked example

For an e-commerce orders domain: the OLTP schema is customers, orders, order_items, payments, all normalized with foreign keys, so that placing an order is a small, fast transaction with ACID guarantees (atomicity, consistency, isolation, durability) touching only the rows involved and never risking an update anomaly on shared data. The OLAP schema built from the same domain, feeding a revenue dashboard, is typically a denormalized fact table like fact_order_line(date_key, customer_key, product_key, quantity, revenue) joined to small dimension tables, so that "total revenue by product category last quarter" is one scan and aggregation over a purpose-built table rather than a multi-way join across the live transactional tables (which would also risk slowing down the transactional workload itself).

Trade-offs and pitfalls

• Running analytical queries directly against the OLTP schema is the most common mistake this comparison is testing for: heavy scans compete for the same locks and buffer-pool space as live transactional writes, so most teams replicate or ETL the OLTP data into a separate OLAP store (a warehouse) rather than analyzing production directly.
• ETL/ELT is the bridge between the two: batch or change-data-capture (CDC) pipelines periodically transform the normalized OLTP shape into the denormalized OLAP shape, meaning the OLAP side is always somewhat stale relative to the OLTP source, by however long that pipeline's latency is.
• The typical enterprise reporting source of truth is the OLAP side, precisely because the OLTP schema is optimized for correctness-under-concurrency, not for the kind of ad hoc, multi-dimensional slicing that reporting needs.

OLTP (normalized) vs OLAP (denormalized/dimensional):

• OLTP: normalized schemas (3NF) minimize redundancy, ensure transactional integrity, and optimize short, concurrent writes/updates (e.g., order entry system). Trade-offs: more joins for complex reads.
• OLAP: denormalized star schemas optimize read performance for analytics, simpler ad-hoc queries, faster aggregates, but increase storage and ETL complexity and can duplicate data.
  Hybrid example: a reporting mart where operational OLTP database remains normalized for transactions while a near-real-time denormalized read replica or materialized views populate a dimensional schema for analytics. Use change data capture (CDC) to feed the data warehouse; this preserves OLTP integrity while supporting fast analytical queries.