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 😊
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
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.
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
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
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.
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.
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?
Hi, I just replied to your other comment. I think one table will probably be better, and have explained more in this video: ruclips.net/video/Pg0ADtwt32k/видео.html 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.
What do you think of this option ? Products: Pk Id Username CurrentVersion IsActive ProductVersions: PK FK ProductId PK Version Name Description Price DateTimeAdded
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.
@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.
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 😊
Thanks! Good to hear you had a few options and discussed them as a team.
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
That sounds like a good approach. Thanks for sharing!
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.
Yeah that's a good point, it has some structure in the JSON or XML and can still be flexible.
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
And also even go on and log data for the create event, that way can really track a record from point of creation.
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
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.
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"?
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.
@@DatabaseStar I like that response, thank you for replying and expanding the scope on that
is Option 2 the same thing as Temporal Tables in SQL Server and if so do you recommend using them?
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.
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?
Hi, I just replied to your other comment. I think one table will probably be better, and have explained more in this video: ruclips.net/video/Pg0ADtwt32k/видео.html
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.
What do you think of this option ?
Products:
Pk Id
Username
CurrentVersion
IsActive
ProductVersions:
PK FK ProductId
PK Version
Name
Description
Price
DateTimeAdded
That looks like it could work. What does the username capture? The user who made the latest change?
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.
@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.