Data Modeling and Schema Design Questions

B-tree vs Bitmap summary:

• B-tree: balanced tree of keys -> good for high-cardinality indexes, equality and range queries; supports concurrent writes with fine-grained locks.
• Bitmap index: bitmaps per distinct value; fast for low-cardinality columns and complex boolean combinations (AND/OR); compact and extremely fast for read-heavy analytic queries.
  When to choose for low-cardinality (gender, booleans):
• Bitmap indexes are attractive for analytic workloads with infrequent DML and lots of complex ad-hoc filtering because they can combine bitmaps cheaply.
• For OLTP/high-concurrency OLTP with frequent updates/inserts/deletes, choose B-tree (or no index) because bitmap indexes often lock entire bitmap segments on updates leading to concurrency bottlenecks (e.g., Oracle warns of bitmap index contention).
  Recommendation:
• If the dimension table is read-mostly and part of analytic queries: create bitmap indexes on low-cardinality columns (or use compressed bitsets in columnar stores).
• If the table is write-heavy / concurrent updates are frequent (e.g., user profile changes), use B-tree indexes or maintain the low-cardinality attributes as plain columns and rely on filters plus bloom filters in query engines.
  Concurrency considerations:
• Bitmap index updates can serialize, causing lock contention on DML; avoid bitmap indexes on frequently updated tables.
• B-tree scales better for mixed read/write workloads; costlier in storage for low-cardinality but safer under concurrency.

Direct answer

Track promotion redemptions with three tables: promotions (the rule definition), promotion_assignments (which users are eligible for which promotion), and promotion_redemptions (which assignments were actually used), so that redemption rate is a simple ratio between the last two tables.

Structured elaboration

• promotions(promotion_id PK, code, discount_type, discount_value, is_one_time_use, valid_from, valid_to).
• promotion_assignments(assignment_id PK, promotion_id FK, user_id FK, assigned_at): one row per user made eligible for a promotion, whether by targeting, opt-in, or automatic enrollment.
• promotion_redemptions(redemption_id PK, assignment_id FK, order_id FK, redeemed_at, is_one_time_use): one row per actual use. The is_one_time_use column is a copy of the flag from promotions, captured at redemption time so the one-time-use rule can be enforced with a partial unique index scoped to this table alone (a unique index cannot reference columns on another table, so the flag has to be denormalized onto the redemptions row itself to be enforceable this way).

Metrics fall out directly: redemption_rate = COUNT(DISTINCT promotion_redemptions.assignment_id) / COUNT(DISTINCT promotion_assignments.assignment_id) per promotion, and time_to_redemption = redeemed_at - assigned_at, averaged per promotion.

Worked example

CREATE TABLE promotions (
  promotion_id   BIGINT PRIMARY KEY,
  code           TEXT NOT NULL UNIQUE,
  discount_value NUMERIC(10,2) NOT NULL,
  is_one_time_use BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE promotion_assignments (
  assignment_id  BIGINT PRIMARY KEY,
  promotion_id   BIGINT NOT NULL REFERENCES promotions(promotion_id),
  user_id        BIGINT NOT NULL,
  assigned_at    TIMESTAMP NOT NULL,
  UNIQUE (promotion_id, user_id)
);

CREATE TABLE promotion_redemptions (
  redemption_id  BIGINT PRIMARY KEY,
  assignment_id  BIGINT NOT NULL REFERENCES promotion_assignments(assignment_id),
  order_id       BIGINT NOT NULL,
  redeemed_at    TIMESTAMP NOT NULL,
  is_one_time_use BOOLEAN NOT NULL   -- copied from promotions.is_one_time_use when the redemption is written
);

-- Enforces "at most one redemption per assignment" only for assignments flagged
-- one-time-use; multi-use promotions are free to redeem the same assignment
-- more than once.
CREATE UNIQUE INDEX one_time_redemption_uq
  ON promotion_redemptions (assignment_id)
  WHERE is_one_time_use;

This was verified in sqlite3 (which supports partial indexes): for a one-time-use promotion, a second INSERT referencing the same assignment_id is correctly rejected by the partial unique index; for a non-one-time-use promotion (is_one_time_use = false), a second redemption against the same assignment_id correctly succeeds, since that row falls outside the partial index's WHERE clause. A plain, unconditional UNIQUE constraint on assignment_id (with no WHERE clause) would have blocked legitimate repeat redemptions for multi-use promotions too, which is why the constraint has to be partial rather than blanket.

Trade-offs and pitfalls

• Concurrency is the real risk with one-time-use promotions: two simultaneous checkout requests for the same user and promotion could both pass an application-level "already redeemed?" check before either commits; the partial UNIQUE index is what actually prevents the double-redemption race, by making the database itself reject the second insert rather than relying on a check-then-act race in application code.
• The is_one_time_use value on promotion_redemptions is a denormalized copy of the flag on promotions, captured at write time; if the promotion's flag ever changes after assignments already exist, already-written redemption rows keep whatever flag they were written with, so a promotion-level flag change does not retroactively tighten or loosen enforcement on rows written before the change.
• For promotions with stacking rules, conditional eligibility (new customer only, minimum basket size), expiration, and partial refunds, this schema is a floor, not a ceiling: those rules typically live in a separate eligibility-evaluation layer that reads promotions and the customer/order context at checkout time, rather than being encoded as extra columns on these three tables, because the rules themselves change far more often than the redemption-tracking shape does.
• Redemption rate as defined here silently excludes users who were never assigned in the first place; if the real question is "what fraction of ALL customers used this promotion," you need to join against the full customer base, not just promotion_assignments, which measures conversion only among those who were targeted.

Direct answer

For the given e-commerce schema, customers.customer_id and orders.order_id and order_items.order_item_id are the primary keys; orders.customer_id and order_items.order_id are the foreign keys; the relationships are customer-to-orders (one-to-many) and order-to-order_items (one-to-many), and the columns worth inspecting first for data quality are the foreign keys themselves (orphan checks) and any nullable business-critical column.

Structured elaboration

• Primary keys: customers.customer_id, orders.order_id, order_items.order_item_id.
• Foreign keys: orders.customer_id -> customers.customer_id; order_items.order_id -> orders.order_id.
• Relationships and cardinality: one customer places many orders (one-to-many, customers to orders); one order contains many order items (one-to-many, orders to order_items). There is no direct relationship stated between order_items and any product table here, only a plain product_id column with no declared foreign key shown.
• Columns to inspect first for data quality: the two foreign-key columns, for orphaned references (an orders.customer_id with no matching customers row, or an order_items.order_id with no matching orders row); orders.total_amount for plausibility (negative or zero values on a placed order); and order_items.quantity/price for the same reason, since these directly drive any revenue calculation built on top of this schema.

Worked example

A concrete first-pass check: SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL surfaces any order referencing a customer that doesn't exist in the customers table, which is exactly the kind of silent data-quality problem that's invisible from just reading the schema definition but shows up immediately once you check the actual data against the declared relationships.

Trade-offs and pitfalls

• A schema diagram alone tells you the intended structure, not whether the data actually honors it; without an enforced foreign-key constraint in the database itself, orphaned rows can and do accumulate, which is why checking for them is the first concrete step rather than an afterthought.
• order_items.product_id having no declared foreign key in this schema (as given) is itself worth flagging: it may simply not be shown, or it may genuinely be unconstrained, in which case verifying referential integrity against a products table becomes an even higher priority check.
• Reading cardinality off a schema correctly (one-to-many here, not many-to-many) matters because it changes what a COUNT or SUM over a join actually represents; joining orders to order_items will legitimately multiply rows per order, which is correct for line-item-level analysis but wrong if you wanted one row per order.

Direct answer

Denormalization is appropriate when read latency and throughput matter more than write simplicity and storage, and when the same joined shape is queried repeatedly, most commonly in read-heavy analytics and dashboard workloads. The right technique to keep the denormalized copy correct depends on how fresh it needs to be: triggers and change-data-capture (CDC, streaming a source table's changes into another store) for near-real-time, batch ETL for anything that tolerates minutes-to-hours of staleness.

Structured elaboration

Benefits and drawbacks, side by side:

Techniques to keep denormalized data correct, roughly in order of freshness guarantee (strongest to weakest):

1. Synchronous triggers inside the same transaction as the source write. Strongest consistency, but couples write latency to however expensive the trigger is, and triggers are easy to lose track of during schema changes.
2. Change-data-capture (CDC) streaming (log-based, e.g., Debezium) into the denormalized table asynchronously. Near-real-time, decoupled from the source transaction, but introduces a small propagation lag and needs idempotent upserts to survive replays.
3. Batch ETL on a schedule (hourly/nightly). Simplest to build and reason about, but the denormalized copy is stale by definition between runs, which is fine for a daily sales dashboard and wrong for an inventory count shown at checkout.

Worked example

A common concrete pattern: an orders_summary reporting table that flattens orders, customers, and order_items into one wide row per order, refreshed nightly via batch ETL, specifically to serve an "orders-by-customer" dashboard that was previously timing out because it joined three normalized tables on every page load. Another pattern in the opposite direction: a blog platform storing comment_count directly on the posts table (a precomputed aggregate) rather than running COUNT(*) over comments on every page view, updated via a trigger that increments/decrements on insert/delete of a comment. Both are denormalization; the first trades freshness for query simplicity at read time, the second trades a small amount of write-path complexity for an O(1) read instead of an aggregation.

Trade-offs and pitfalls

• The two scenarios where you should keep things normalized: (1) the data changes frequently and the "single source of truth" property is load-bearing, such as an account balance that must never be allowed to drift from the ledger of transactions that produced it; (2) the query pattern is genuinely ad hoc and unpredictable, so there's no one denormalized shape that would serve most queries, and pre-computing the wrong shape wastes storage and adds staleness risk for no benefit.
• A frequent mistake is denormalizing before confirming the join is actually the bottleneck; add the denormalized copy after profiling shows the joins dominate query time, not preemptively.
• Whichever technique you pick, always keep the normalized source as ground truth and treat the denormalized copy as a rebuildable cache: if it ever drifts, you should be able to regenerate it from the source rather than needing to reconcile it by hand.

Partitioning by date improves performance by pruning irrelevant partitions, improving I/O, and enabling parallel processing and easier maintenance.
For 10 years of daily transaction lines use RANGE partitioning by month (e.g., partition per month) or per-day if query patterns require very fine pruning. Monthly partitions balance number of partitions (~120) vs partition size; daily partitions (~3650) can be heavier to manage.
Scheme: RANGE on order_date using monthly boundaries. Benefits: most queries filter by month/day so planner prunes partitions; maintenance (drop/archive old partitions) is straightforward; backfill and bulk loads target single partitions. Use partitioned indexes or global indexes depending on DB. Choose daily only if queries frequently filter by exact day and single-day partitions are required for performance.