-1

currently I found myself wondering if this is the right thing to do in this case.

You see I have a database called for example Warehouse

I this database I have two tables:

[the table items is for saving the items of the system add-modify-delete records]

  1. Items (which its columns are )
    • TAG_ID
    • P_NUMBER
    • QUANTITY
    • TYPE
    • LOCATION
    • DESCRIPTION
    • REASON
    • USERID
    • DATE

[the table lastchanges is for saving the items of the system that has been changed]

  1. lastchanges (which its columns are )
    • ID
    • TAGID
    • PNUMBER
    • USERID
    • ACTION
    • DATING

Now I have been asked to add "exactly what has been changed" to the current form, for example if the quantity changed I have to show that before and after in a bootstrap form.

My brain told me to just add all the columns of the table items into lastchanges and save on those columns the data before changing and into items the new modified data, but performance-wise I see this as a bad action and I want your opinion.

Script47
  • 14,230
  • 4
  • 45
  • 66
Hex
  • 47
  • 6
  • *[...]and I want your opinion.* - ***Sorry,*** *Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.* – Script47 Aug 29 '17 at 09:08
  • Related: https://stackoverflow.com/questions/39281/database-design-for-revisions – Progrock Aug 29 '17 at 09:10

1 Answers1

1

If I understand you correctly you need a history of your DB changes.

If thats the point I would recommend you to create a new row for each entry and soft delete the old one. Then nothing gets lost and you can always get differences or older values.

Adding a the field deleted_at, and created_at as dates would do that trick for you. If deleted_at is null its the current entry, if there is a date set you know exactly when it got "overwritten"

MartinLeitgeb
  • 63
  • 6
  • the problem is he needs to know what has been changed, theres the item 'a' that has all those column values, but he needs to know for example if quatity changed and what it is now and what it was before. – Hex Aug 29 '17 at 09:22
  • 1
    In this case a changes table would be needed including the table name and the field name. Then you can get the current value and the old value via the suggested date fields. – MartinLeitgeb Aug 29 '17 at 09:25
  • So should I follow my brain's advice, I have two ways of doing this, but the two are very inefficient 1. I add all items columns to lastchanges table and just leave the old data in the lastchanges columns so when I'm going to show the data I pull the new from items and the old from lastchanges. 2. I create a column in lastchanges and just separate the columns and values with for example a '~' – Hex Aug 29 '17 at 09:30
  • I'd say yes, do it with the lastchanges table – MartinLeitgeb Aug 29 '17 at 09:33
  • I think separating the values with a divisor should be more efficient, it will be a pain in client side, but its something any computer should do. – Hex Aug 29 '17 at 13:43