Databases
StackOverflow2013
Postsdbo
POHow to keep track of historical changes to reference table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to keep track of historical changes to reference table?
    primarykey
    data
    text
    <p>This is a very common thing in web applications. If I have a user table and I want to keep track of all the changes made to the user table, I can use a database insert and update triggers to save those changes in the user_history table.</p> <p>But what if I have user_products table, where I have user_id , product_id, cost. When I add a user in the system lets say I have two products associated with that user. So my user_products table will have two rows for that user. </p> <pre><code>user_id product_id cost 1 10 1000 2 20 2000 </code></pre> <p>Now if I go to the edit user page and delete product 1 , add product 3 , change cost for product 2 from 2000 to 3000. </p> <p>so normally I delete all records for user_id 1 from user_product table and then do a new insert for the new products. </p> <p>So its not a regular update but a delete and then insert. Hence I am not able to keep track of history changes. </p> <p>Ideally I would want to know that I deleted product 1 , added product 3 , changed cost for product 2 from 2000 to 3000. </p> <p><strong>EDIT 1:-</strong></p> <p>I am not doing a update. I am doing a delete and then insert. So I am deleting record with product id 2 and cost 2000. And then again inserting record with prod id 2 but with cost 3000. So technically its delete and insert but logically only cost is changed from 2000 to 3000. If i check while executing both queries it will say i deleted product with id 2 and and then added product with id 2 which are same. But I want to be able to see that the cost has chnaged from 2000 to 3000</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USashishjmeshram
    1. USashishjmeshram
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to keep track of historical changes to reference table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to keep track of historical changes to reference table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy not just do an update rather than a delete and an insert?
      singulars
      1. POHow to keep track of historical changes to reference table?
      1. USDan A.
    2. CO@Dan A. Its basically one to many relationship from user to product. User can have 100s of products. If user has 10 products now. He may have 40 products later on. If I add a new product to user I will have to check if that user product mapping exist in table or not. If yes then update and if not then do insert. So for me its easy to just delete everything and then insert all products again. This way i dont have to check for existing and new records and run insert or update query accordingly.
      singulars
      1. POHow to keep track of historical changes to reference table?
      1. USashishjmeshram
    3. COAny convenience you gain from not checking for the existence of products to update is lost when you try to track history. In my opinion it's better practice to perform a "logical update" by actually doing a "technical update". What if you decide to add columns "create_date" and "last_updated" to your product record? It's trivial to do if you update an existing record.
      singulars
      1. POHow to keep track of historical changes to reference table?
      1. USDan A.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload