Data Modeling and Schema Design Questions

Star schema: central denormalized fact table linked to flat dimension tables (no joins between dimensions). Snowflake: dimensions normalized into multiple related tables.
Star advantage: simpler queries and faster joins for OLAP tools; easier to understand. Star disadvantage: dimension redundancy increases storage and can require more ETL work to maintain consistency.
Snowflake advantage: reduced redundancy and smaller storage for large, highly normalized dimensions; better for maintaining domain integrity. Snowflake disadvantage: more complex queries with additional joins, slightly worse query performance and complexity in BI tools.
Choose star for performance and simplicity; choose snowflake when dimension normalization is needed to enforce hierarchical attributes or save substantial space.

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.

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.

Direct answer

For semi-structured product metadata, a JSON column is the better default when attributes vary heavily by category and change often, while fully normalizing into relational columns is better when the same handful of attributes are shared across most products and need to be reliably filterable and indexable; most real catalogs land on a hybrid of both.

Structured elaboration

• Queryability: normalized columns support efficient, indexable equality and range filters natively (WHERE color = 'red' AND size = 'M'); a JSON column requires either JSON-path query syntax (slower without a specific index) or extracting fields at query time.
• Indexing: normalized columns get ordinary B-tree indexes; a JSONB column can be indexed too (a GIN index in Postgres supports efficient containment queries), but indexing a JSON column is coarser-grained and generally less efficient than a dedicated column index for a specific, stable, frequently-filtered attribute.
• Schema evolution: a JSON column absorbs a new, category-specific attribute with zero migration; adding a new relational column for every possible category-specific attribute (battery_capacity for electronics, screen_size for a different category) leads to a very wide table full of NULLs for products where that attribute doesn't apply.
• Storage: JSONB is reasonably compact and the values are stored parsed (unlike plain JSON, which stores raw text), but a value repeated across many rows (a common category name, say) isn't as compactly stored or as easy to enforce consistency on as a normalized lookup table with a foreign key.

Worked example

For semi-structured event data specifically (a related but distinct case), there are three concrete strategies worth naming: (1) store the raw JSON and use JSON path functions at query time, maximum flexibility, worst per-query performance since nothing is pre-extracted; (2) flatten into a wide table with one column per known field, best query performance but a schema migration every time a new field appears; (3) extract only the frequently-queried fields into real columns while leaving the rest in a JSON "overflow" column, a middle ground that captures most of the performance benefit for the fields that matter without requiring a migration for every rarely-used field.

Trade-offs and pitfalls

• The most common mistake is treating this as an all-or-nothing decision; the practical pattern for a product catalog is a hybrid: normalize the small set of attributes shared across (nearly) all products and used for cross-category filtering (price, name, category_id), and keep a attributes JSONB column for the long tail of category-specific fields.
• Indexing a JSONB column with a GIN index supports containment queries (attributes @> '{"color":"red"}') reasonably well, but composite filters across multiple JSON keys plus a normalized column don't optimize as cleanly as an equivalent all-relational composite index; if a specific JSON field turns out to be extremely commonly filtered, promoting it to its own indexed column (even while keeping the rest in JSON) is usually worth the one-time migration.
• Schema evolution for the JSON side still needs discipline even without a database migration: a convention for field naming, a shared understanding of which fields are required per category, and ideally a validation layer (a JSON schema check at write time) to prevent the same logical attribute from being spelled two different ways across different writers.

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.