The Best Way To Add Audit Tables to Your Database

[@dhruvitmodi3043]

Hi, I am working as SQL developer, recently we completed data model for one project, In which we go with option 1 for audit log. We discussed both options 1 and 2, but finalise same table for audit log as there won't be lots of data that may cause performance issue. Btw cheers for your efforts, love your videos 😊

[@DatabaseStar]

Thanks! Good to hear you had a few options and discussed them as a team.

[@luisedwards3534]

What I usually do is option three with a combination of option 2! With the difference that instead of doing option 2 exactly as mentioned in the video, I just add the created_by, updated_by, deleted_by, created_at, updated_at, deleted_at fields to the most important or usually the most used tables

[@DatabaseStar]

That sounds like a good approach. Thanks for sharing!

[@SerenityMusicOasis]

A better approach IMO to option 2 or 3 is to serialize the original row into a XML or JSON into the general audit table. This way is flexible enough to handle any source table structure and you don't need multiple rows to save every field that changed. Just my 2 cents.

[@DatabaseStar]

Yeah that's a good point, it has some structure in the JSON or XML and can still be flexible.

[@jirehla-ab1671]

hi @@DatabaseStar I remember that oracle offered a free entperise oracle 21c db for for free for production use only, but the free developer edition of 23c isnt what I am lookin for since it has 12gb ram limitation as opposed to free 21c entperise edtion for non-commercial use only. Has oracle changed there terms, may u try contacting oracle about this since I am not a bunsiess owner , regarding this, thanks

[@fleboho]

And also even go on and log data for the create event, that way can really track a record from point of creation.

[@3alegions]

Hi, your videos are so helpful, I have a question, I'm working on a CMS, The CMS has two separate sites, a public site and an admin panel, should I have 2x tables, tblUser for Public site login and tblAdminUsers for Admin site login or just one table tblUsers two store both users? I’m aware I can apply roles to each user and based on roles I can manage logins but Admin user will have only few columns of information on the other hand the public users will have a lot of columns, so if I make one table for both users, many columns will have null values for Admin Users… What would you do in such scenarios?

[@DatabaseStar]

Hi, I just replied to your other comment. I think one table will probably be better, and have explained more in this video: kzbin.info/www/bejne/hpiTcneqrNlmaM0 It's OK for a table to have many NULL columns if they don't apply, so I don't think it's a bad approach.

[@RiGoRmOrTiSUK]

is Option 2 the same thing as Temporal Tables in SQL Server and if so do you recommend using them?

[@DatabaseStar]

Yes I think it's very similar. It uses the same concept. I discovered temporal tables pretty recently, and they look like a good feature to use: they have a lot of this "audit table" functionality but it's built in, so it's easier to maintain.

[@Yalchin403]

How about inserting deleting and updating the original table and at the same time record each change of state in the separate audit table and for fetching the records we can use orignal table only

[@DatabaseStar]

Yeah that’s what the audit table will do. It will record the changes in the audit table and keep the up to date information in the main table.

[@kodekorp2064]

If the "ROW_KEY" is a randomly assigned key value by the database that can change at a later time, why would you audit the "ROW_KEY"?

[@DatabaseStar]

Good question. It depends on whether you consider that as an update you want to track. Maybe you do want to track it so you know when it was changed and what it changed from and to.

[@kodekorp2064]

@@DatabaseStar I like that response, thank you for replying and expanding the scope on that

[@gregorymoore2877]

The row_key itself would (should) not change. It tells you which row in the actual table changed. This makes it possible to restore data to a previous state if it is discovered that a recent change was incorrect.

[@mariewebb2017]

What do you think of this option ? Products: Pk Id Username CurrentVersion IsActive ProductVersions: PK FK ProductId PK Version Name Description Price DateTimeAdded

[@DatabaseStar]

That looks like it could work. What does the username capture? The user who made the latest change?

[@mariewebb2017]

Sorry, I should have deleted the username. I designed something similar to hold a user's online post. The user is the owner of the post. If a visitor later reports that the Post is not appropriate, we can review all versions to verify if there was an issue.

[@marikselazemaj3428]

@mariewebb2017 This is exactly what I am doing in my project. My main goal was to achieve an immutable strategy, something like a version control. If you don't mind me asking, have you implemented this already? How did it go, what database are you using? Thanks in advance.