dcsimg
Results 1 to 6 of 6

Thread: Best practice with db design

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    14

    Best practice with db design

    I'm about to design a database to automate some processes and also to monitor the changes being made by its user. I was thinking of making a dedicated "UpdatedBy" column for each table but i've just learned that there's something called Change Data Capture for SQL Server. I'm still weighing my options whether to use SQL Server or MySQL since the company is on a tight budget. Should I push thru with the UpdatedBy column or just go with Change Data Capture?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: Best practice with db design

    CDC provides a lot of information and, as a result, requires a lot of space to store the data. You may end up with significantly more audit data than actual data. That's something you have to live with if you need that level of detail. If all you need to know is who the last user to change a record was then a column that stores that is all you need. One point to note is that CDC enables physical deletes, where in-record auditing limits you to logical deletes. Of course, a physical delete with CDC means an insert of the same record data plus more in a change table, so it depends what you think you're gaining.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,984

    Re: Best practice with db design

    There's also Change Tracking in SQL Server, which is like Change Data Capture but doesn't record the actual data that changed, so doesn't have the same storage overhead. Not sure what the minimum version is for support of each option.

  4. #4
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,429

    Re: Best practice with db design

    It also depends on if you will be using an application or straight SQL.
    With an application you can choose where and how you can log you data and in what type (log file, xml ,sql etc).
    With straight up SQL I'm not sure. I would have a look at what JMC says . There is another suggestion that I'm thinking of not giving it here cuz it seems easy but on occasions is a very bad solution....Anyhow, triggers. You can trigger what you need to log on the user and and do, whatever is you need to do. As I've said I haven't deal with straight up logging on SQL so probably this is a bad solution but i though to expand the field a little.
    Slow as hell.

  5. #5
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    943

    Re: Best practice with db design

    Generally I have used modified by and timestamp columns.

    Not knowing the real business requirements of your change-tracking needs, another more intense option would be to use the MySQL binlogs (with a format of ROW) in some fashion. All changes are tracked since that is what is used for replication. I'd guess it's not for the faint of heart, but I see there are at least some Python tools that could perhaps provide ideas.

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    14

    Re: Best practice with db design

    Thank you jmcilhinney , sapator and topshot. I'll try the columns for now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width