How would you model address history and versioning?

How would you model address history and versioning?

• A. Use a separate AddressVersion or AddressHistory table linked to address_id with version_number, effective_from, effective_to, changed_by, and change_reason fields to snapshot each change.
• B. Store history in a dedicated History table linked to address_id and implement versioned rows with effective_from and effective_to timestamps.
• C. Rely on a created_at timestamp and an updated_at timestamp on the main row; no separate history.
• D. Maintain a separate archiving system outside the database for historical addresses.

Answer: (B)

Modeling address history with a history table that stores each version and its validity window is the right approach because it makes time-based queries straightforward and accurate. By linking each version to the address_id and recording effective_from and effective_to, you can answer questions like “What was the address on a specific date?” by simply finding the version whose interval contains that date. This pattern, often called a type 2 slowly changing dimension, keeps every change in sequence and preserves the full history without overwriting earlier data.

In practice, you keep one row for the current version (often with a far-future or null effective_to) and, when a change occurs, close the old version by setting its effective_to to the change date and insert a new row with the updated address and the new effective_from. This gives you a clean, efficient mechanism for auditing, reporting at any point in time, and reconstructing any past state.

Why the other approaches are less ideal in this context: relying on created_at and updated_at only records the most recent state and doesn’t preserve past values in an easily queryable way; snapshotting with version numbers and extra metadata adds complexity and bloat without inherently enabling precise point-in-time queries; and maintaining history outside the database saves nothing if you still need to reassemble timelines and often leads to synchronization and consistency problems.