Data Modeling medium
A user changes their address. You need to keep their old address for historical reporting of past orders. Which pattern fits best?
- A SCD Type 2: A new row is added for the new address with a specific validity range, ensuring that historical orders remain linked to the address used at that time.
- B SCD Type 1: The system simply overwrites the existing address record with the new data, assuming that historical accuracy is not a requirement for the domain.
- C SCD Type 3: The table schema is modified to include a single `previous_address` column that acts as a basic, one-step history for the most recent change.
- D SCD Type 0: The user record is deleted and recreated from scratch to ensure that all database-level internal pointers and caches are fully refreshed.
Answer: A. SCD Type 2: A new row is added for the new address with a specific validity range, ensuring that historical orders remain linked to the address used at that time.
Slowly Changing Dimensions (SCD) Type 2 is the industry standard for historical reporting. It allows you to maintain multiple versions of an entity over time, which is critical if you need to know exactly which tax rate or shipping zone was active when a specific order was placed years ago. The cost is a significant increase in storage requirements and more complex query logic for joining tables.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?