Data Modeling and Schema Design Questions

Direct answer

For a multi-tenant healthcare application storing protected health information (PHI), separate-database-per-tenant (or at minimum separate-schema-per-tenant) is usually the right default, because HIPAA (the US Health Insurance Portability and Accountability Act)-driven tenant isolation, per-tenant backup/retention policies, and encryption-key scoping are all far simpler to prove and audit when tenants are physically separated, even though it costs more operationally than a shared schema.

Structured elaboration

• Shared schema with tenant_id: cheapest to operate at scale (one schema, one connection pool, easy cross-tenant analytics), but every single query must correctly filter by tenant_id, and a single missing filter (an application bug, a forgotten WHERE clause) leaks PHI across tenants; per-tenant backup/restore and per-tenant retention policies (a tenant asking to delete all their data on a specific date) require selective, row-level operations rather than clean physical boundaries.
• Separate schema per tenant: each tenant's tables live under their own schema in a shared database instance. Better isolation than shared-schema (a query mistake is scoped to the connection's schema search path, not a missing filter), but backups, encryption keys, and heavy resource usage are still shared at the database-instance level, so a noisy or breached tenant can still affect others.
• Separate database per tenant: strongest isolation. Per-tenant backup/restore, per-tenant retention/deletion, and per-tenant encryption keys are all natively database-level operations rather than something application code has to get right on every query. The cost is operational: provisioning, migrating schema changes, and monitoring N databases instead of one.

Worked example

A concrete compliance scenario that decides this: a HIPAA Business Associate Agreement requires that Tenant A's data be fully deletable, with proof of deletion, within a defined window after contract termination, without touching Tenant B's data. Under shared-schema, that "delete everything for tenant A" operation is a large, application-driven DELETE ... WHERE tenant_id = ? across every table, which is slow, easy to miss a table in, and hard to produce an audit-grade "proof of complete deletion" for. Under separate-database-per-tenant, the same requirement becomes "drop (or securely wipe) tenant A's database," a single, auditable, database-level operation with a clean boundary.

Trade-offs and pitfalls

• Separate-database-per-tenant is materially more expensive operationally: schema migrations must be rolled out to every tenant database individually (or via tooling that automates it), and cross-tenant reporting (if ever legitimately needed, e.g., aggregate usage metrics for the business itself) requires a separate ETL/replication step rather than a simple cross-tenant query.
• Least-privilege access control is easiest to reason about with separate databases (a support engineer's credentials can be scoped to exactly one tenant's database) and hardest with shared schema (row-level security policies must be correctly configured and never bypassed).
• A common middle ground for cost-sensitive smaller tenants is schema-per-tenant with row-level security as a defense-in-depth backstop, reserving fully separate databases for the largest or most compliance-sensitive tenants (enterprise healthcare customers with the strictest contractual requirements), rather than applying one tenancy model uniformly to every tenant regardless of size or risk profile.

Direct answer

A composite primary key works by combining two or more columns so that the combination is guaranteed unique, even if no single column is; here (order_id, product_id) guarantees a given product appears at most once per order, while still letting the same product appear on many different orders and the same order contain many different products.

Structured elaboration

CREATE TABLE order_items (
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  price      NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Joining to orders and products is unaffected by the key being composite: orders joins on order_id, products joins on product_id, exactly as it would with a single-column key:

SELECT o.order_id, p.name, oi.quantity, oi.price
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id;

Worked example

Verified in sqlite3: inserting (order_id=1, product_id=100) twice (say, two separate lines both adding the same product to the same order) fails on the second insert with a primary-key violation, confirming the composite key correctly prevents a duplicate line for the same product on the same order. If the business actually wants to allow two separate lines for the same product on the same order (for example, two different discount codes applied to two units of the same product), that's exactly when you'd prefer a surrogate order_item_id instead: the composite key as given structurally assumes one row per (order, product) pair, which is a real business rule, not just an implementation detail.

Trade-offs and pitfalls

• The composite key here is enforcing a genuine business rule (one line per product per order), not just providing uniqueness for its own sake; if that rule is ever wrong (the two-discount-codes scenario above, or wanting separate lines for auditability), the fix is a surrogate order_item_id plus, if the one-line-per-product rule still needs to hold in the common case, a separate UNIQUE(order_id, product_id) constraint that can be relaxed later without changing the primary key.
• A common mistake is adding a surrogate key "just in case" without checking whether the composite key was already expressing a real invariant worth keeping; dropping the composite uniqueness silently allows the exact duplicate-line bug the original design prevented.
• At scale, a composite key clusters the table by order_id first (in engines that physically order by the primary key), which is usually the right locality for "all items in this order," but means "all orders containing this product" needs a secondary index on product_id alone rather than relying on the primary key.

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.

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.