Data Modeling and Schema Design Questions

Goal: keep fast point-selects but preserve scan throughput in columnar store.
Design choices:

• Maintain clustered sort keys so frequently point-selected columns (primary key or lookup key) are leading in sort order; this allows range reads to be localized.
• Use zone maps/min/max per segment to skip segments quickly for scans and to identify segments for point selects.
• Build a lightweight secondary index (sparse or bloom-filter index) mapping key -> segment/file offset to avoid scanning whole dataset for points; keep index stored row-oriented (or in separate KV engine) for constant-time lookups.
• Keep data partitioned by high-cardinality dimensions used in point selects (e.g., date or customer_id) so point queries target fewer segments.
• For frequently-accessed small subsets, maintain a small row-oriented cache/materialized table (hot-store) updated in near real-time; fall back to columnar for large scans.
  Trade-offs: secondary indexes and caches increase storage and write cost but give low-latency point queries while columnar layout remains optimized for scans. Monitor write amplification and tune compaction/partitioning.

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.

Problem: many LEFT JOINs with nullable surrogate FKs causing repeated NULLs and heavy joins. Schema fixes: 1) Replace nullable FK patterns by introducing a canonical 'unknown' sentinel row in the master table and default FK to that non-null value—removes NULLs and simplifies joins. 2) Denormalize frequently used master attributes into dimension tables or a flattened reporting table. 3) Create materialized views or precomputed denormalized reporting tables to avoid repeated joins. Query-level optimizations: 1) Use indexed partial joins or filtered indexes on non-sentinel keys. 2) Rewrite LEFT JOINs to INNER JOIN where FK guaranteed by ETL, or use COALESCE(master.attr, '<unknown>') to avoid null proliferation. 3) Use bitmap or covering indexes for common predicates. Result: fewer expensive LEFT JOINs, simpler queries, and improved planner estimates leading to faster execution.

Direct answer

A relational database can technically answer friends-of-friends and shortest-path queries via recursive joins, but a graph database is purpose-built for multi-hop traversal and will outperform it substantially as the number of hops or the graph's size grows, because a graph engine walks physical adjacency pointers per node rather than repeatedly re-joining large tables.

Structured elaboration

The core access-pattern difference: a relational join for "friends of friends" requires self-joining the friendships table once per hop (friends JOIN friends AS friends_of_friends ON ...), and each additional hop multiplies both the join complexity and, in the worst case, the intermediate result size; a graph database stores each node's adjacent edges directly reachable from the node itself, so traversing to a neighbor, then to ITS neighbor, is a constant-time pointer-following step per hop rather than a table scan and join.

Explaining this to a stakeholder without assuming a data-modeling background: "In a spreadsheet-like table, finding your friends' friends means scanning the whole friendships list twice and matching rows together; in a graph database, it's like each person already has a physical list of who they know taped to them, so finding a friend's friend is just walking down two lists directly, without searching anything."

Worked example

A practical phased approach to prove value without disrupting existing services: (1) stand up the graph database as a secondary store, fed by CDC or a batch export from the existing relational friendships table, running alongside the relational system unchanged; (2) route only the specific slow query (friends-of-friends recommendations, say) to the new graph store, A/B testing latency and correctness against the existing relational implementation; (3) once the graph store proves out on latency and correctness for that one query, expand its scope to other graph-shaped queries (shortest path, mutual-friend counts), while leaving anything relational-shaped (a user's own profile fields, billing) exactly where it already lives. This avoids a risky big-bang migration and lets the stakeholder see a concrete, measured win before committing further.

Trade-offs and pitfalls

• The relational self-join approach isn't wrong, it's just increasingly expensive: for one or two hops on a modest-sized friendship graph, a well-indexed relational query can still perform acceptably, and introducing a whole new database technology for a query that's not actually a bottleneck yet is premature.
• A hybrid, not a full migration, is usually the right end state: transactional, tabular data (profiles, billing, settings) stays relational, where ACID guarantees and simple lookups are exactly what's needed, while genuinely graph-shaped queries (multi-hop relationships, shortest path, community detection) move to the graph store, rather than trying to force one engine to be good at both.
• Explaining the trade-off in terms of hop count specifically (not "graphs are just better") is what makes the stakeholder conversation productive: the relational approach degrades predictably and measurably as hop count grows, which gives a concrete threshold to point to rather than an abstract preference.

Direct answer

A PRIMARY KEY uniquely identifies every row, is implicitly NOT NULL, and a table can have only one; a UNIQUE constraint also enforces uniqueness but allows NULLs (depending on the database, typically any number of NULLs, since NULL is not considered equal to another NULL) and a table can have several UNIQUE constraints. Both create an index that can be referenced by a foreign key.

Structured elaboration

• Purpose: PRIMARY KEY declares the table's main identity, the column(s) every foreign key elsewhere in the schema will typically reference; UNIQUE enforces a secondary uniqueness rule (an email address, a username) without making that column the table's identity.
• Nullability: PRIMARY KEY columns are always NOT NULL by definition; a UNIQUE constraint's column(s) CAN be NULL, and most databases (Postgres included) allow multiple NULL rows in a UNIQUE column, since NULL is treated as "unknown," and two unknowns are not considered duplicates of each other.
• Index behavior: both create a unique index under the hood, so both give you fast equality lookups; a PRIMARY KEY's index is also, by convention, what the storage engine physically clusters or orders the table by in some databases (though Postgres itself doesn't physically cluster by PK unless you explicitly run CLUSTER).
• Referencing by foreign keys: a foreign key can reference either a PRIMARY KEY or a column with a UNIQUE constraint (in Postgres, a foreign key target must have a unique index, which either kind satisfies); it does not have to specifically be the primary key.

Worked example

-- (a) primary key on a single column
CREATE TABLE users (
  user_id BIGINT PRIMARY KEY,
  email   TEXT NOT NULL,
  referral_code TEXT
);

-- (b) unique constraint on a nullable column
ALTER TABLE users ADD CONSTRAINT users_referral_code_unique UNIQUE (referral_code);
-- referral_code can be NULL for users with no referral, and Postgres allows multiple such NULLs

-- (c) creating a unique index manually (equivalent enforcement, different syntax)
CREATE UNIQUE INDEX users_email_unique_idx ON users (email);

This was verified against a real PostgreSQL instance: the referral_code column must exist on users before the ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (referral_code) statement can succeed (Postgres rejects it with column "referral_code" named in key does not exist otherwise), so it is declared directly in the CREATE TABLE rather than assumed. With the column present, both the constraint and the manual unique index were created successfully, and inserting two rows with referral_code IS NULL succeeded without a uniqueness violation, confirming multiple NULLs are allowed under UNIQUE.

Practical composite choices: a partial unique index (CREATE UNIQUE INDEX ON subscriptions (user_id, product_id) WHERE status = 'active') enforces "at most one active subscription per user/product pair" while still allowing many historical, cancelled subscriptions for the same pair, which a plain UNIQUE(user_id, product_id) constraint could not express.

Trade-offs and pitfalls

• Assuming a UNIQUE constraint behaves exactly like PRIMARY KEY except for the "only one per table" rule is a common misconception; the NULL-handling difference (multiple NULLs allowed under UNIQUE, impossible under PRIMARY KEY since it's always NOT NULL) is a real, frequently-tested distinction.
• Composite UNIQUE constraints are index-order-sensitive for query planning just like any other composite index: UNIQUE(a, b) serves lookups filtering on a alone efficiently but not lookups filtering on b alone, the same caveat that applies to any multi-column index.
• Partial unique indexes are a Postgres-specific (and a few other engines') feature that isn't portable to every database; if cross-database portability matters, the same "unique among active rows only" rule may need to be enforced with a trigger or application-level check instead.