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 valid-time versioning is the right approach: store each version of an address as its own row tied to the address, and give that row a time interval during which it is in effect (effective_from to effective_to). This lets you answer questions like “what did this address look like on 2023-06-01?” by simply finding the row for that address whose interval covers the date. Because the data in each row represents the actual state during that interval, queries and audits stay clean and straightforward, and you don’t have to piece together changes from metadata.

The selected approach uses a separate history table linked to the address, with versioned rows defined by their validity window. This is cleaner for temporal queries and preserves a complete, continuous history of the address as it existed over time, without relying on external inference or on per-change snapshots that require extra logic to reconstruct the current or past states.

Storing only created_at and updated_at on the main row won’t give you true history: you’d struggle to reconstruct past values after updates, since you’d be missing the prior state. An external archiving system adds complexity and siloed data, making timely, in-database point-in-time queries harder and potentially breaking referential integrity. And while snapshotting each change with a separate history table is workable, it’s more verbose and requires extra logic to align snapshots with current data and to interpret which snapshot represents which point in time. The time-interval approach is typically more scalable and query-friendly for historical and versioned data.