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 😊
@DatabaseStar10 ай бұрын
Thanks! Good to hear you had a few options and discussed them as a team.
@SerenityMusicOasis11 ай бұрын
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.
@DatabaseStar11 ай бұрын
Yeah that's a good point, it has some structure in the JSON or XML and can still be flexible.
@jirehla-ab167111 ай бұрын
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
@fleboho16 күн бұрын
And also even go on and log data for the create event, that way can really track a record from point of creation.
@kodekorp20642 ай бұрын
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"?
@DatabaseStar2 ай бұрын
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.
@kodekorp20642 ай бұрын
@@DatabaseStar I like that response, thank you for replying and expanding the scope on that
@RiGoRmOrTiSUK2 ай бұрын
is Option 2 the same thing as Temporal Tables in SQL Server and if so do you recommend using them?
@DatabaseStar2 ай бұрын
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.
@3ALEGIONS11 ай бұрын
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?
@DatabaseStar11 ай бұрын
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.
@Yalchin4037 ай бұрын
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
@DatabaseStar7 ай бұрын
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.
@mariewebb20177 ай бұрын
What do you think of this option ? Products: Pk Id Username CurrentVersion IsActive ProductVersions: PK FK ProductId PK Version Name Description Price DateTimeAdded
@DatabaseStar7 ай бұрын
That looks like it could work. What does the username capture? The user who made the latest change?
@mariewebb20177 ай бұрын
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.