Data Modeling and Schema Design Questions

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.

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.

Schema: sensor_readings(sensor_id, ts TIMESTAMP, value, quality, ingestion_ts, PRIMARY KEY(sensor_id, ts DESC) or use (sensor_id, ts) with clustering by sensor_id+ts DESC). Columns: sensor_id, ts, value (numeric), status/quality, tags, offset. Primary key choice: composite key with sensor_id first to enable contiguous writes and range scans per sensor. Indexing: clustered/sort key on (sensor_id, ts DESC) for fast latest-value and efficient range scans. Maintain a separate latest_values(sensor_id PK, latest_ts, value, quality) table updated via upserts or streaming to serve instant latest queries. Retention: time-based partitioning per month or per sensor bucket, with TTL job to drop/compact partitions; use downsampling aggregates (hourly/daily) stored in rollup tables. For high write throughput: use batch inserts, append-only writes, partitioning, and use LSM-based stores (Cassandra/ClickHouse/TimescaleDB) or write-optimized engines. Trade-offs: separate latest table gives O(1) reads; background retention/compaction minimizes storage.

Direct answer

Partitioning splits a large table into smaller physical pieces (partitions) based on a chosen key, so queries and maintenance operations only need to touch the relevant partitions instead of the whole table; the three common schemes are range, hash, and list, chosen by how your queries typically filter the data.

Structured elaboration

• Range partitioning: partitions cover contiguous ranges of a key, most often a date (e.g., one partition per month). Best when queries commonly filter by that range (WHERE created_at BETWEEN ...), because the planner can skip entire partitions outside the range (partition pruning).
• Hash partitioning: a hash function distributes rows evenly across a fixed number of partitions. Best when there's no natural range to filter on and the goal is simply to spread write and storage load evenly, avoiding one partition becoming a hot spot.
• List partitioning: partitions are defined by explicit, discrete values of a key (e.g., one partition per region or country_code). Best when queries commonly filter on a small, known set of categorical values and you want each category isolated for maintenance (e.g., dropping all data for a deprecated region in one operation).

Worked example

An orders table with billions of rows, mostly queried by recent date range, is a textbook range-partitioning case: PARTITION BY RANGE (created_at) with one partition per month. A query for "orders in March 2026" only scans the March partition, ignoring the other 11+ months entirely (partition pruning), and archiving old data becomes a metadata operation (DETACH PARTITION, then drop or move it to cheap storage) instead of a slow DELETE that has to scan and remove billions of rows one at a time.

Trade-offs and pitfalls

• Maintenance you must plan for: creating new partitions ahead of time (an unpartitioned "future" write will either fail or need a default partition), periodically dropping or archiving old partitions, and keeping per-partition statistics (ANALYZE/VACUUM in Postgres) up to date, since a huge partitioned table with stale statistics on individual partitions can still produce bad query plans.
• A poorly chosen partition key produces the opposite of the intended benefit: range-partitioning by a column that's rarely used in WHERE clauses means every query still scans every partition, and hash-partitioning on a low-cardinality column can leave some partitions far larger than others (a skewed hot partition).
• Partitioning is a physical-storage decision layered on top of the logical schema, not a replacement for good indexing; a well-chosen partition key reduces how much data a query has to consider, but you generally still need indexes within each partition for anything beyond the partition-pruning win itself.

Direct answer

Backward and forward compatibility for an evolving API or message schema both rest on the same principle: only add optional things, never repurpose or remove what already exists, so an old reader can ignore fields it doesn't recognize and a new reader can supply sensible defaults for fields an old writer never sent.

Structured elaboration

• In a relational database: add new columns as nullable (or with a default), never repurpose an existing column's meaning, and treat removing a column as a multi-step deprecation (stop writing to it, confirm nothing reads it, then drop it), the same expand-contract discipline used for any schema migration.
• In a message schema (Avro/Protobuf): both formats have explicit compatibility rules for exactly this scenario. Protobuf: every field has a unique numbered tag; adding a new field with a new tag number is safe for both old and new readers (old readers simply ignore the unrecognized field, new readers see a default if an old writer never sent it); renaming a field is safe (only the tag number matters on the wire, not the name); but reusing a tag number for a different field, or changing a field's type incompatibly, breaks compatibility. Avro: relies on both the writer's and reader's schema being available at deserialization time, with defined rules for how a reader schema resolves fields present in the writer schema but not the reader's (and vice versa), which is why Avro deployments typically pair with a schema registry to track compatibility across versions centrally.

Worked example

A concrete safe evolution: an Order message adds a new optional discount_code field with a fresh tag number. Old consumers, compiled against the schema before the change, simply never see discount_code and continue working unaffected (forward compatibility: old code tolerates new data). New consumers reading a message produced by an old producer that never set discount_code see it as unset/default (backward compatibility: new code tolerates old data). Contrast with an UNSAFE change: renaming a field's tag number, or changing discount_code from a string to a nested message, both of which would misinterpret bytes on the wire for whichever side didn't get the corresponding update at the same time.

Trade-offs and pitfalls

• The single most common way this compatibility discipline breaks in practice is repurposing a field for a new meaning without changing its identifier (its tag number, or its column name); this passes casually because the schema still "type-checks," but it silently misinterprets historical data or in-flight messages produced under the old meaning.
• A schema registry (for Avro/Protobuf in a streaming system) or an automated compatibility-check step in CI (for a relational migration) is what turns "we're supposed to follow these rules" into "a violation is caught before it ships," which matters because a compatibility violation is often invisible in a single service's own tests and only surfaces when an old and new version run side by side in production.
• Backward and forward compatibility are genuinely two different guarantees and it's easy to satisfy one while breaking the other; a new required (non-optional) field satisfies "old readers tolerate new data" (nothing about old readers changed) but breaks backward compatibility, since new readers now expect the field on every message, and an old writer's message, produced with no knowledge of the new requirement, would be missing it and rejected as invalid by the new reader.