Mysql – Design for user profile information and track changes

database-designMySQL

I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like:

  • street
  • city
  • zip code
  • mobile phone
  • country
  • contact name

And the user's email and a hash of their password (of course).

I know storing all of this data in one table is a bad idea, but I cannot come up with a better solution for now. Can you help me with that and suggest a way of dividing this data into different tables?

I would also like to keep track of all changes that user does with his account data (physical address/name/phone number/email/password hash).

Best Answer

You can follow a proven design from data warehousing called a Slowly Changing Dimension.

Essentially on each row you add some combination of an ActiveFlag, DateTimeStart and DateTimeEnd. When you get a change you end the old record and write a new one, which will include the changes that triggered the historic change. If you do not care about some fields' history you can simply update in place on the active row.

There will only ever be one active row for any given login and any number of historic versions of the information. If you keep the start date and end dates you can see when changes occurred in addition to simply keeping older records around. You can always move ended rows to a different table, and retain only the most recent record in your system table... I'd make sure that the performance hit and maintenance costs of doing so outweigh simply keeping them in place.

Related Question