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

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.

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.

Grain = the atomic level of detail stored in a fact table.
Three example grains for online retail:

1. Order-line grain: one row per order line (order_id, product_id, quantity, line_total). Use when analyzing product-level sales and fulfillment.
2. Order-level grain: one row per order (order_id, customer_id, order_total). Use for customer lifetime and order-level KPIs.
3. Daily-store-product grain: one row per product per day per store (date, store_id, product_id, units_sold, revenue). Useful for fast time-series reporting.
   Practical consequence of wrong granularity: too-coarse (order-level) loses ability to analyze product-level metrics; too-fine (per-click) inflates storage, slows ETL and queries and complicates aggregates. Choose grain to match reporting needs and retention constraints.

Definitions:

• 1NF: atomic values, single-valued cells.
• 2NF: 1NF + no partial dependency on a composite PK (each non-key attribute depends on whole PK).
• 3NF: 2NF + no transitive dependencies (non-key attributes depend only on PK).
  Example violating 2NF:
  Table: order_product(order_id, product_id, product_name, product_price, quantity)
  Sample row: (1001, 200, "Widget", 9.99, 2)
  Here product_name/price depend only on product_id (part of composite PK), causing partial dependency.
  Transform to 2NF:
• order_items(order_id, product_id, quantity) -- PK (order_id, product_id)
• products(product_id, product_name, product_price) -- PK product_id
  Now product attributes live in products and order_items stores only quantity. This removes partial dependencies and redundancy.