MySQL在更改条目时优化数据库中的数据存储

Question

We faced with a problem:

When we are generating accounts (or other documents), we use customer (employee) data. However, customers can change their data (e.g. woman get married and her last name changed, or she moved to live in other country and her address changed).

We need, that after editing woman last name, accounts remain with old last name until specific date,and after that specific date, use woman new last name.

e.g. We have a woman: Emily Smith. She married 2014 December 15, and her last name become Brown. Before: December 15 we generate her account with Smith last name but after December 15 we need to generate her account with Brown last name. So in database we need to save both her last names.

How to save entry optimized with only one or a few changed attributes?

P.S. We also need to save the date when the change was made in order to know which last name to use, old or new.

Answer 1

This is called a slowly changing dimensions. The traditional approach is to have records with an effective date and end date attached. So, Emily would have two rows in this table:

AccountId    Firstname     LastName     EffDate      EndDate
   1           Emily       Smith        2001-01-01   2014-12-14
   1           Emily       Brown        2014-12-15   NULL

Often, the NULL value for the current record would be some arbitrary date far in the future (such as 9999-12-31). Not having to deal with NULLs in the logic can improve perfromance.

Then, when you want the record as of a particular date, you just use the record that is valid on that date. If you want the current record, the same logic holds -- find the record valid today.