1 Replies - 1029 Views - Last Post: 30 September 2013 - 07:50 AM Rate Topic: -----

#1 guyfromri   User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 836
  • Joined: 16-September 09

Basic DB design concepts - auditing data changes

Posted 30 September 2013 - 07:03 AM

Hi Guys,

This is pertaining to MySQL but it's not a direct MySQL question so I'm asking it in general.

I'm building a database to track clients and I'm curious if anyone with more experience would be able to offer some insight.

I have to track phones, addresses, web addresses, etc. I could go two ways with this;

<b>Option 1</b>
Separate tables for each piece of info. When changes are made, deactivate old info and add new line with existing info + updates. This seems like it would do the trick but on a bigger scale could get costly with overhead.

<b>Option 2</b>
One table to track all information and one table to log changes. So if a client changes an address, I can log the previous value, current value and the date of the change.

Option 2 seems like the obvious answer to me but I have no formal training with DB development and I wanted to see if there was anything I wasn't considering.

As always, thanks in advance!!

Is This A Good Question/Topic? 0
  • +

Replies To: Basic DB design concepts - auditing data changes

#2 modi123_1   User is online

  • Suitor #2
  • member icon

Reputation: 14095
  • View blog
  • Posts: 56,488
  • Joined: 12-June 08

Re: Basic DB design concepts - auditing data changes

Posted 30 September 2013 - 07:50 AM

I would go with option two; modified.

If you need to track previous addresses then certainly have only one 'active' address (be it a bit column or a null value for 'date deactivated').... and previous addresses have a bit column flipped or just an entry in the 'date deactivated' column.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1