The Best Way To Add Audit Tables to Your Database

Поделиться
HTML-код
  • Опубликовано: 27 дек 2024

Комментарии • 21

  • @dhruvitmodi3043
    @dhruvitmodi3043 Год назад +5

    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
      @DatabaseStar  Год назад

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

  • @luisedwards3534
    @luisedwards3534 Месяц назад +1

    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
      @DatabaseStar  Месяц назад

      That sounds like a good approach. Thanks for sharing!

  • @SerenityMusicOasis
    @SerenityMusicOasis Год назад +5

    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
      @DatabaseStar  Год назад

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

    • @jirehla-ab1671
      @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
      @fleboho 3 месяца назад

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

  • @Yalchin403
    @Yalchin403 10 месяцев назад

    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
      @DatabaseStar  10 месяцев назад

      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
    @kodekorp2064 5 месяцев назад +1

    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
      @DatabaseStar  5 месяцев назад +1

      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
      @kodekorp2064 5 месяцев назад

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

  • @RiGoRmOrTiSUK
    @RiGoRmOrTiSUK 5 месяцев назад

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

    • @DatabaseStar
      @DatabaseStar  5 месяцев назад

      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.

  • @3ALEGIONS
    @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
      @DatabaseStar  Год назад

      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.

  • @mariewebb2017
    @mariewebb2017 10 месяцев назад

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

    • @DatabaseStar
      @DatabaseStar  10 месяцев назад

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

    • @mariewebb2017
      @mariewebb2017 10 месяцев назад

      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
      @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.