DEA-C01 Question 263
Single answerYou are designing a data model for an e-commerce platform that tracks customer orders, product inventory, and shipment details. The platform frequently queries the status of orders and product availability, while shipment details are less frequently accessed. Which data modeling approach is most suitable to optimize performance and scalability in this scenario?
- A
Denormalized model where order, product, and shipment data are combined into a single table
- B
Normalized model with separate tables for orders, products, and shipments with relationships defined between them
- C
Star schema with orders as the fact table and products and shipments as dimension tables
- D
Key-value model in a NoSQL database where each order is stored as a single record with embedded product and shipment details
Show answer and explanation
Correct answer: C
Explanation
A star schema is well-suited for scenarios where there are frequent analytical queries, such as checking order statuses and product availability. By structuring the data into a fact table and related dimensions, it optimizes query performance while maintaining a scalable and flexible model. This approach also supports the less frequent access of shipment details without compromising the efficiency of the primary queries.
- A. Incorrect.
A denormalized model may lead to redundant data and is not efficient when shipment details are less frequently accessed. This can result in unnecessary storage and update overheads.
- B. Incorrect.
A normalized model ensures data integrity but may introduce performance overhead due to the need for frequent joins, which is suboptimal when querying order statuses and product availability.
- C. Correct.
A star schema with orders as the fact table and products and shipments as dimensions provides an optimized structure for frequent queries. It balances performance and scalability by minimizing joins for common queries while maintaining a clear data structure.
- D. Incorrect.
A key-value model in a NoSQL database may work for highly unstructured data but can be inefficient for queries that require filtering or joining across different data entities like orders, products, and shipments.