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.

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.

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

ON DELETE/ON UPDATE actions decide what happens to a dependent row when the row it references is deleted or its key changes: CASCADE propagates the change, SET NULL clears the reference, and RESTRICT/NO ACTION block the change entirely while any dependent rows exist; the right choice depends on whether the dependent row's existence is meaningful without its parent.

Structured elaboration

• CASCADE: deleting a users row also deletes all of that user's orders. Appropriate when the dependent row has no independent meaning without its parent (a user's shopping-cart items, say), but dangerous when the dependent rows themselves have standalone business value (deleting a user should probably not silently delete their entire order history).
• SET NULL: deleting a users row sets orders.referred_by_user_id to NULL instead of deleting the order. Appropriate when the reference is informational, not load-bearing (knowing who referred a customer is nice to have, but an order remains a valid, meaningful record even if the referrer's account is later deleted).
• RESTRICT/NO ACTION: block the delete entirely while any referencing row exists, forcing an explicit decision (reassign or manually remove the dependents first). Appropriate as the default for anything financially or legally significant, where a cascading or silently-nulled deletion could quietly destroy or corrupt a record that must be preserved.

Worked example

For users and orders: orders.user_id should almost certainly be RESTRICT or NO ACTION, not CASCADE, because deleting a user account should never silently delete their entire purchase and payment history; the correct operational flow is to first decide what happens to their orders (anonymize, reassign to a "deleted user" placeholder, or archive them) as an explicit step, not as an automatic side effect of the account deletion. By contrast, cart_items.cart_id referencing a carts row is a reasonable CASCADE: an abandoned cart's line items have no independent meaning once the cart itself is gone.

Trade-offs and pitfalls

• The main operational risk of CASCADE is exactly the "accidental mass-delete" scenario: deleting one row at the top of a deep reference chain can silently delete thousands of rows across many tables with no confirmation step, which is especially dangerous when the cascade chain is several levels deep and not all of it is obvious to whoever issued the original delete.
• Preventing accidental mass-deletes: default to RESTRICT for anything where deletion should require an explicit, reviewed decision, reserve CASCADE for genuinely dependent, no-independent-value child rows, and consider soft-deletes (an is_active/deleted_at flag, with no ON DELETE action ever firing because rows are never physically deleted) for anything where the safest default is "never let this disappear automatically at all."
• SET NULL requires the foreign-key column to be nullable, which is easy to overlook when initially defining the column as NOT NULL for data-quality reasons; if SET NULL is the intended behavior, the column's nullability constraint has to be designed for it from the start, not bolted on later.

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.