An e-commerce company uses Cloud Spanner for order processing. They need to query orders by customer ID and retrieve all order items. Which schema design pattern should they use for optimal performance?
Trap 1: Store all data in a single table with nullable columns for order…
This is poor schema design leading to sparsity and inefficiency.
Trap 2: Denormalize by storing order items as a repeated field in the…
Spanner is a relational database; repeated fields are not supported. Denormalization would break relational integrity.
Trap 3: Create two separate tables with a secondary index on customer_id in…
This leads to cross-table lookups and slower queries compared to interleaving.
- A
Use interleaved tables where Orders is the parent and OrderItems is an interleaved child table with the same primary key prefix.
Interleaving co-locates child rows with their parent, enabling efficient joins and strong consistency.
- B
Store all data in a single table with nullable columns for order item attributes.
Why wrong: This is poor schema design leading to sparsity and inefficiency.
- C
Denormalize by storing order items as a repeated field in the orders table.
Why wrong: Spanner is a relational database; repeated fields are not supported. Denormalization would break relational integrity.
- D
Create two separate tables with a secondary index on customer_id in the orders table and a secondary index on order_id in the order_items table.
Why wrong: This leads to cross-table lookups and slower queries compared to interleaving.