Data Modeling and Schema Design Questions

Direct answer

A surrogate key is a system-generated identifier with no business meaning (an auto-increment integer or a UUID); a natural key is a real-world attribute that's already unique (an email address, a national ID). In a large OLTP system, prefer a surrogate key as the primary key and enforce the natural key separately with a UNIQUE constraint, unless the natural key is guaranteed stable, short, and never needs to change.

Structured elaboration

Trade-offs:

• Uniqueness guarantees: a surrogate key is unique by construction; a natural key's uniqueness depends on a real-world guarantee (emails are supposed to be unique, but people change emails, accounts merge, and typos happen) that the business, not the database, actually controls.
• Join performance and index size: a small fixed-width integer surrogate key produces smaller, faster indexes than a variable-length string natural key (an email), which matters at scale across every table that references it.
• Replication/merge complexity: if two systems (or two data centers) need to merge records that used natural keys independently, natural-key collisions or format mismatches (case sensitivity, whitespace) become integration headaches; surrogate keys sidestep the comparison but push the "these two rows are actually the same entity" problem to a separate identity-resolution step.
• Human-readability: a natural key like an order number is meaningful in logs, support tickets, and URLs; a surrogate key is opaque and usually needs a natural-key-like display value anyway for humans.

Worked example

For customers(customer_id BIGINT PRIMARY KEY, email TEXT UNIQUE NOT NULL) and orders(order_id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers, ...): every foreign key across the system references the compact customer_id integer, not the variable-length email. If a customer later changes their email, that's a single-row update to customers.email and nothing else has to change, because nothing else stored the email as a join key. Had email been the primary key instead, every table referencing it, plus every place that logged or cached it, would need updating when the email changes, which is exactly the fragility surrogate keys are designed to avoid.

Trade-offs and pitfalls

• A pure natural key becomes especially fragile the moment the "immutable, unique forever" assumption breaks; social security numbers get reissued in rare cases, emails get reused after account deletion, and any of those breaks a chain of foreign keys that assumed permanence.
• The common middle ground, and usually the right one, is: surrogate key as the primary key for joins, natural key kept as a UNIQUE NOT NULL column for lookups and human-facing operations, giving you the stability of the surrogate and the readability/query convenience of the natural key.
• Surrogate keys are not free: they add one layer of indirection (you often have to look up the surrogate key before you can query by the natural key the user actually typed), and if generated naively (plain auto-increment) they leak information about record counts and creation order, which occasionally matters for security or business-confidentiality reasons.

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.

Problem: top-10 products by avg rating last 30 days under heavy writes. Goals: fast aggregate reads without slowing writes.
Schema changes and indexes:

• Add a write-optimized summary table reviews_agg(product_id, window_day date, review_count int, rating_sum int, rating_avg float) updated incrementally.
• Maintain recent-window rolling buckets, e.g., daily or hourly buckets, then compute 30-day averages by aggregating these buckets.
• For low-latency leaderboard, maintain a materialized view or in-memory cache (Redis/KeyDB) keyed by day and product with sorted sets for top-N.
  Implementation:
• On insert of review, write to reviews table (append-only) and asynchronously push a lightweight event to a background worker/queue (Kafka/RabbitMQ).
• Worker updates reviews_agg: increment count and sum for current day using atomic DB statements (UPSERT) or update via idempotent increments.
  Indexes:
• Primary key on reviews_agg(product_id, window_day) for fast upsert.
• Index on reviews_agg(window_day, rating_avg DESC) to compute top-N per day; or maintain precomputed leaderboard in cache (sorted set by avg).
  Why this helps:
• Heavy writes: main write stays append-only with minimal synchronous work; aggregation is handled asynchronously, avoiding write contention and expensive full-table scans.
• Aggregation: computing top-10 for last 30 days becomes a small aggregation over 30 rows per product (or precomputed daily scores), or merging top lists from cache shards.
  Edge cases:
• Ensure idempotency and eventual consistency; provide fallback exact query (slower) if aggregator lag is unacceptable. Use background workers with retries and metrics.

Requirements: query price at any timestamp; aggregate revenue by price band per day. Approach: use append-only price history table with effective ranges, plus denormalized price-on-order snapshots and partitioning.
Schema:

• product_price_history(product_id, price_cents, effective_from TIMESTAMPTZ NOT NULL, effective_to TIMESTAMPTZ, source varchar, PRIMARY KEY(product_id, effective_from))
• orders(order_id, product_id, qty, price_snapshot_cents, order_ts)
  Storage & indexes:
• Partition product_price_history by date-range of effective_from for efficient prune.
• Index on (product_id, effective_from) and on (product_id, effective_from, effective_to) or use tstzrange and GiST index for faster time-slice lookup.
  Typical queries:
• Price at time t: SELECT price_cents FROM product_price_history WHERE product_id = X AND effective_from <= t AND (effective_to IS NULL OR effective_to > t) LIMIT 1; (use index on product_id,effective_from)
• Revenue by price band per day: ensure orders store price_snapshot_cents at purchase time; then run GROUP BY date_trunc('day', order_ts), CASE WHEN price_snapshot BETWEEN band1 THEN ... END, SUM(price_snapshot_cents*qty).
  Handling multiple changes per day:
• Keep product_price_history as high-resolution (per-change). For aggregations, rely on orders.price_snapshot to avoid expensive joins to history.
  Alternative: if you must compute revenue by price band for historical times when price changed but orders lack snapshot, join orders to price_history using GiST range index on tstzrange(effective_from, effective_to) to find price at order_ts. Partitioning and indexes are crucial to avoid full scans.
  Trade-offs: Storing price snapshot duplicates data (more storage) but makes aggregations fast and stable; history table kept compacted and partitioned for retention.

Direct answer

The conceptual model for an on-demand food-delivery platform needs roughly ten entities: Orders, Customers, Restaurants, Menus, MenuItems, Drivers, Deliveries, Payments, Addresses, and Promotions, with the central cardinality being that one Order has many Deliveries-worth of state transitions but exactly one Delivery record, and many-to-many relationships appearing between Restaurants and MenuItems (via Menus) and between Orders and Promotions.

Structured elaboration

Core entities and representative attributes:

• Customer: customer_id, name, email, phone, created_at, is_active.
• Address: address_id, customer_id, line1, city, postal_code, is_default. One-to-many from Customer.
• Restaurant: restaurant_id, name, cuisine_type, address_id, is_active, average_prep_time.
• Menu: menu_id, restaurant_id, name, effective_from, effective_to. One-to-many from Restaurant (a restaurant can have multiple menu versions over time).
• MenuItem: menu_item_id, menu_id, name, price, category, is_available. Many MenuItems per Menu.
• Order: order_id, customer_id, restaurant_id, status, placed_at, total_amount, delivery_address_id.
• Driver: driver_id, name, vehicle_type, is_active, current_status.
• Delivery: delivery_id, order_id (one-to-one with Order), driver_id, picked_up_at, delivered_at, distance_km.
• Payment: payment_id, order_id, amount, method, status. One-to-one (or one-to-many, for split/partial payments) with Order.
• Promotion: promotion_id, code, discount_type, discount_value, valid_from, valid_to. Many-to-many with Order via an order_promotions junction table.
• Log (an audit trail of order-status transitions): log_id, order_id, previous_status, new_status, changed_at.

Cardinalities: Customer 1:N Address; Restaurant 1:N Menu 1:N MenuItem; Customer 1:N Order; Restaurant 1:N Order; Order 1:1 Delivery; Driver 1:N Delivery; Order 1:1 (or 1:N for split payments) Payment; Order M:N Promotion (via junction); Order 1:N Log.

Worked example

Assumptions worth stating explicitly before finalizing the model: timestamps should be stored in UTC with the customer's/restaurant's local timezone kept as a separate attribute for display, not baked into the stored value; MenuItem needs versioning (an effective_from/effective_to range, or a separate menu_item_versions table) so that a historical order can always show the exact name and price shown to the customer at checkout time, even after the restaurant later changes the menu; soft-deletes (is_active flags) are used for Customer, Restaurant, and Driver rather than hard deletes, since orders and deliveries must remain resolvable after any of these become inactive.

Trade-offs and pitfalls

• The most common mistake in this kind of open-ended conceptual model is treating Delivery as just another attribute of Order instead of its own entity; splitting it out is what lets a single order be tracked through pickup/en-route/delivered states with its own timestamps and driver assignment, independent of the order's own lifecycle.
• Modeling MenuItem without versioning is the second common mistake: without it, a price change to a menu item retroactively changes what every past order appears to have charged, which is both a correctness and a billing-dispute problem.
• The Order-to-Promotion many-to-many relationship is easy to under-model as a single promotion_id column on Order; that only works if an order can have at most one promotion, and most real platforms eventually need to support stacked or combinable promotions, at which point the junction table is required anyway.