Data Modeling and Schema Design Questions

Dialect: PostgreSQL. Approach: use surrogate key, effective_from/effective_to timestamps, current_flag, and unique constraint on natural key + effective_from.
Schema and DML:

CREATE TABLE customer_scd2 (
  customer_scd_id BIGSERIAL PRIMARY KEY,
  customer_id UUID NOT NULL,
  name TEXT,
  tier TEXT,
  effective_from TIMESTAMPTZ NOT NULL DEFAULT now(),
  effective_to TIMESTAMPTZ,
  is_current BOOLEAN NOT NULL DEFAULT true
);
-- index for lookups of current record
CREATE UNIQUE INDEX ux_customer_current ON customer_scd2(customer_id) WHERE is_current;
CREATE INDEX ix_customer_eff_from ON customer_scd2(customer_id, effective_from);

On tier change (pseudo-code):

1. UPDATE existing current row set effective_to = now(), is_current = false WHERE customer_id = :id AND is_current;
2. INSERT new row with new tier and effective_from = now(), is_current = true.
   Constraints & indexes:

• Primary surrogate key.
• Partial unique index on (customer_id) WHERE is_current to ensure single current row.
• Index on (customer_id, effective_from) for time-travel lookups.
• Optional exclusion/trigger to prevent overlapping effective ranges if using ranges.
  This preserves history, supports lookups by timestamp (WHERE customer_id = X AND effective_from <= t AND (effective_to IS NULL OR effective_to > t)).

Requirements: allow per-domain (e.g., payments) point-in-time recovery (PITR) with minimal restore time and minimal blast radius. Approach: combine schema partitioning, physical separation, WAL/transaction-log retention per DB or per tablespace, and logical change capture.
High-level pattern:

• Isolate domains into separate schemas/databases or separate tablespaces/physical volumes so backups/restores can target only payment storage.
• Use append-only history tables for critical domains (immutable rows) with effective_from timestamps and soft-delete flags so point-in-time state is reconstructable by time filters rather than in-place updates.
• Enable continuous WAL shipping and base backups per database; for finer granularity use logical replication (pglogical) streaming of payment tables to a standby replica that can be restored to arbitrary WAL position.
  DB features to use:
• Tablespaces / separate physical disks or separate DB instances per domain to reduce restore scope.
• PITR via base backup + WAL for whole DB; for per-domain fast restore, maintain a dedicated replica for that domain and use logical replication to replicate changes to a secondary DB you can rewind/truncate to a timestamp quickly.
• Use change-data-capture (CDC) into an append-only store (object storage or dedicated DB) with event timestamps. To restore payments to t, reapply events up to t into a new payments table/DB—fast because only payments events are processed.
  Schema patterns:
• Event sourcing / append-only tables: each change is a new row with event_time, event_type, and payload. Reconstruct state by replay or materialized views up to desired time.
• Time-range partitioning by event_date for history tables so restoring a segment is file-level (partition) restore—restore only partitions touching the time window.
• Soft-delete and effective_from/effective_to columns for current-state tables and history tables for quick snapshot queries.
  Other considerations:
• Maintain compact incremental backups for payment partitions; use snapshot isolation and consistent backup points.
• Provide automated tooling: a restore orchestrator that can restore partitions or replay CDC to target timestamp and verify constraints.
  Trade-offs: separate DBs increase operational complexity and cross-domain transactions become distributed (use two-phase commit or compensate). Append-only and CDC increase storage but greatly reduce restore time and risk.

Direct answer

For a sub-second dashboard, denormalized pre-aggregated tables win on latency because they avoid computing joins and aggregations at query time, at the cost of freshness (the aggregate lags behind the source) and extra write amplification (every source-row change must also update the aggregate).

Structured elaboration

The four axes to reason through:

• Freshness: a normalized OLTP query always reflects the current state exactly; a pre-aggregated table reflects whatever state existed the last time it was refreshed. If the dashboard needs true real-time counts, the aggregate must be updated synchronously or via low-latency streaming, not batch.
• Write amplification: every insert/update to the source table that affects an aggregate now triggers a second write (an increment, a trigger-fired update, or a streaming re-aggregation), roughly doubling the write cost for that logical event.
• Storage cost: pre-aggregating trades a small amount of extra storage (the aggregate table itself, usually orders of magnitude smaller than the source) for large latency wins, so storage is rarely the limiting factor here.
• Maintenance complexity: normalized OLTP tables have one shape to maintain; adding an aggregate means maintaining a second artifact that must be kept consistent with the first, including handling late-arriving or corrected source rows.

Worked example

Concretely: a normalized query computing "total orders and revenue in the last hour" by scanning and joining orders and order_items directly might need to scan a few hundred thousand recent rows and join them, taking hundreds of milliseconds to seconds depending on indexing, which fails a sub-second SLA under load. A pre-aggregated hourly_summary(hour_bucket, order_count, revenue) table, incrementally updated as each order is placed, turns the same dashboard query into a single-row lookup by hour_bucket, typically sub-millisecond. The trade a team accepts here is that the current (in-progress) hour's bucket is only as fresh as the last increment, and a late-arriving refund or correction to an order placed 10 minutes ago must also correctly decrement the right bucket, not just insert a new row.

Trade-offs and pitfalls

• The single biggest pitfall is treating the aggregate as authoritative once it's built: if the increment logic ever double-counts or misses an event (a common bug with retries or out-of-order delivery), the aggregate silently drifts from the true source-of-truth total and nobody notices until the numbers look implausible.
• A second common pitfall is under-provisioning for late corrections: refunds, cancellations, and backdated adjustments must decrement or re-bucket the aggregate correctly, and a naive "increment on insert, never decrement" design will overstate revenue forever.
• The decision isn't binary: many teams keep the normalized tables as ground truth and periodically reconcile the aggregate against a full recompute (nightly, say) specifically to catch any drift the incremental path introduced, accepting that reconciliation cost as the price of a correct sub-second read path.

Direct answer

For the given e-commerce schema, customers.customer_id and orders.order_id and order_items.order_item_id are the primary keys; orders.customer_id and order_items.order_id are the foreign keys; the relationships are customer-to-orders (one-to-many) and order-to-order_items (one-to-many), and the columns worth inspecting first for data quality are the foreign keys themselves (orphan checks) and any nullable business-critical column.

Structured elaboration

• Primary keys: customers.customer_id, orders.order_id, order_items.order_item_id.
• Foreign keys: orders.customer_id -> customers.customer_id; order_items.order_id -> orders.order_id.
• Relationships and cardinality: one customer places many orders (one-to-many, customers to orders); one order contains many order items (one-to-many, orders to order_items). There is no direct relationship stated between order_items and any product table here, only a plain product_id column with no declared foreign key shown.
• Columns to inspect first for data quality: the two foreign-key columns, for orphaned references (an orders.customer_id with no matching customers row, or an order_items.order_id with no matching orders row); orders.total_amount for plausibility (negative or zero values on a placed order); and order_items.quantity/price for the same reason, since these directly drive any revenue calculation built on top of this schema.

Worked example

A concrete first-pass check: SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL surfaces any order referencing a customer that doesn't exist in the customers table, which is exactly the kind of silent data-quality problem that's invisible from just reading the schema definition but shows up immediately once you check the actual data against the declared relationships.

Trade-offs and pitfalls

• A schema diagram alone tells you the intended structure, not whether the data actually honors it; without an enforced foreign-key constraint in the database itself, orphaned rows can and do accumulate, which is why checking for them is the first concrete step rather than an afterthought.
• order_items.product_id having no declared foreign key in this schema (as given) is itself worth flagging: it may simply not be shown, or it may genuinely be unconstrained, in which case verifying referential integrity against a products table becomes an even higher priority check.
• Reading cardinality off a schema correctly (one-to-many here, not many-to-many) matters because it changes what a COUNT or SUM over a join actually represents; joining orders to order_items will legitimately multiply rows per order, which is correct for line-item-level analysis but wrong if you wanted one row per order.

Direct answer

Storing created_at as TIMESTAMP WITHOUT TIME ZONE means the column silently records whatever local time the writer happened to be in, with no record of which timezone that was; the fix is to change the column to TIMESTAMPTZ and normalize all writes to UTC at ingestion, not to try to reconstruct timezone information after the fact from a column that never captured it.

Structured elaboration

• The risk across regions: if application servers in different regions write to this column using their own local clock (or worse, a mix of local time and UTC depending on which service wrote the row), two rows with the identical stored value can represent two genuinely different real-world instants, and there is no way to tell which is which after the fact, because the timezone information was never captured.
• Normalizing for analysis: the only fully correct fix is to know, for each historical row, what timezone or UTC offset it was actually written in; if that's recoverable (e.g., from a separate region column on the same table, or from deployment history correlated with created_at's range), you can backfill a corrected UTC value. If it's not recoverable, the historical data has an irreducible ambiguity that should be documented, not silently "fixed" with a guessed offset.
• Schema change vs. enforce-at-ingestion: the schema itself should change to TIMESTAMPTZ (which in Postgres always stores UTC internally and converts to/from a timezone only at display time), and the application layer must be fixed to always write in UTC (or with an explicit, correct offset) going forward; changing only the column type without fixing the writers just moves the same ambiguity into a differently-typed column.

Worked example

-- schema fix, step 1: correct any KNOWN non-UTC subset FIRST, while the column is still naive,
-- so the later blanket conversion doesn't reinterpret it a second time.
-- Reinterpret the naive value as the writer's actual local time, then re-express it as the
-- equivalent naive UTC wall-clock value (this two-step "AT TIME ZONE" is what actually corrects it):
UPDATE events
SET created_at = (created_at AT TIME ZONE 'America/Los_Angeles') AT TIME ZONE 'UTC'
WHERE region = 'us-west' AND created_at < '2026-03-01';

-- schema fix, step 2: now that every row's naive value is genuinely UTC, do the blanket type change
ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at AT TIME ZONE 'UTC';

The USING clause is doing real work here: TIMESTAMP WITHOUT TIME ZONE AT TIME ZONE 'UTC' reinterprets the naive timestamp as if it were UTC and produces a correct TIMESTAMPTZ; if the true original offset was NOT UTC for some rows, that reinterpretation is silently wrong for exactly those rows, which is why any known region-specific writer needs its own explicit correction pass, run BEFORE the blanket conversion, not after. Running the targeted correction after the column is already TIMESTAMPTZ does not fix it: confirmed by executing both orderings, converting a naive '2026-02-15 10:00:00' value that was actually written in America/Los_Angeles (true UTC instant 2026-02-15 18:00:00+00) using the blanket-first, correct-second order in this answer's original text produced 2026-02-15 02:00:00+00, which is wrong in the opposite direction; running the correction first, as shown above, produced the correct 2026-02-15 18:00:00+00.

Trade-offs and pitfalls

• The single most dangerous mistake here is applying one blanket AT TIME ZONE conversion to the whole table when the true originating offset actually varied by region or by writer over time; that "fixes" the type but silently bakes in a wrong absolute time for every row that wasn't actually UTC to begin with.
• A closely related mistake is getting the ORDER of operations backwards: any known region-specific correction must be applied to the still-naive column before the blanket ALTER ... USING ... AT TIME ZONE 'UTC' runs, not after. Once the blanket conversion has already relabeled every naive value as UTC, a single further AT TIME ZONE step applied to the resulting TIMESTAMPTZ does not correctly undo and reapply the right offset; it needs an explicit reversal step, so it is simpler and less error-prone to fix known-wrong subsets first, while they're still naive, and only then run the one blanket conversion.
• If the true originating offset for some historical rows is genuinely unrecoverable, converting them anyway to satisfy the new column type without flagging the uncertainty produces confidently-wrong data, which is worse than leaving a documented gap; a data_quality_note or a separate timezone_confidence flag on the affected range is more honest.
• Going forward, enforcing UTC at the application/ingestion layer (not just at the database schema level) is what actually prevents recurrence; a schema type change alone doesn't stop a misconfigured writer from producing a naive local timestamp that then gets miscast as if it were UTC.