dcsimg
Results 1 to 10 of 10

Thread: Best practice with db design

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    16

    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
    101,645

    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
    101,645

    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,459

    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
    986

    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
    Junior Member
    Join Date
    May 2017
    Posts
    16

    Re: Best practice with db design

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

  7. #7
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,337

    Re: Best practice with db design

    Depends on space and which db you use.
    In MS Access I used 'created by'/on 'updated by'/on (two fields each) to get the last date updated and when it was created and by whom.
    However you could also have an audit table that states when the records updated and by whom (but not what).
    Or a copy of the table with the old data being updated, as the audit trail.
    Or a table with who, when, source table, field, old value and new value.

    You can use triggers or put it in the main page code (triggers means you'd track changes to the table direct although you'd need to get the db user, or via form coding (which I used with ms access))

    It mainly depends on how much audit you need, importance and space/money (depending on the db you go with).

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,117

    Re: Best practice with db design

    I previously worked on a system that created "shadow" tables for ones that are audited... the main table included the ID, what ever data it needed, plus, the created by id, date created, changed by id, and date changed. The shadow table then had it's own ID, a record ID (soft pkey back to the main table.ID), an audit key ID, an action field (new, before update, after update, before delete), and then a copy of the same main data fields from the main table.

    in simplistic form:
    Code:
    USER
    -------
    ID
    FNAME
    LNAME
    DEPTID
    PHONE
    CREATEBYID
    DATECREATED
    CHANGEBYID
    DATECHANGED
    
    
    
    
    USER__AUDIT
    ----------------
    ID
    AUDITKEYID
    RECORDID
    ACTION
    FNAME
    LNAME
    DEPTID
    PHONE
    CHANGEDBYID
    CHANGEDDATE
    We then had triggers on the tables that would then track what was changing and by whom, and write to the audit table as necessary what was changing ... it would then also make sure that the change date and changed by id was also set correctly.

    An insert would look like this:
    Code:
    USER
    ------
    1 - Gnome - Tech - 42 - 555.5551212 - 12 - 2019-01-18 14:35:00 - 12 2019-01-18 14:35:00
    
    USER_AUTID
    -------------
    1 - 1 - 1 - Added - Gnome - Tech - 42 - 555.555.1212 - 12 - 2019-01-18 14:35:00
    If I then update a field... say swapping the first & last name... the tables then look like this:

    Code:
    USER
    ------
    1 - Tech - Gnome - 42 - 555.5551212 - 12 - 2019-01-18 14:35:00 - 12 2019-01-18 14:40:00
    
    USER_AUTID
    -------------
    1 - 1 - 1 - Added - Gnome - Tech - 42 - 555.555.1212 - 12 - 2019-01-18 14:35:00
    2 - 2 - 1 - Before Update - Gnome - Tech - 42 - 555.555.1212 - 12 - 2019-01-18 14:35:00
    3 - 2 - 1 - After Update - Tech - Gnome - 42 - 555.555.1212 - 12 - 2019-01-18 14:40:00

    Now I change the department to ID #50....
    Code:
    USER
    ------
    1 - Tech - Gnome - 50 - 555.5551212 - 12 - 2019-01-18 14:35:00 - 12 2019-01-18 14:42:00
    
    USER_AUTID
    -------------
    1 - 1 - 1 - Added - Gnome - Tech - 42 - 555.555.1212 - 12 - 2019-01-18 14:35:00
    2 - 2 - 1 - Before Update - Gnome - Tech - 42 - 555.555.1212 - 12 - 2019-01-18 14:35:00
    3 - 2 - 1 - After Update - Tech - Gnome - 42 - 555.555.1212 - 12 - 2019-01-18 14:40:00
    4 - 2 - 1 - Before Update - Tech - Gnome - 42 - 555.555.1212 - 12 - 2019-01-18 14:40:00
    5 - 2 - 1 - After Update - Tech - Gnome - 50 - 555.555.1212 - 12 - 2019-01-18 14:42:00

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    16

    Re: Best practice with db design

    Thank you Ecniv.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    16

    Re: Best practice with db design

    Thanks techgnome. A version of this is what i'm actually gonna implement.
    CREATEBYID
    DATECREATED
    CHANGEBYID
    DATECHANGED

    Imma try to learn and make use of triggers. Thank you guys.

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