History ?

Databases
1

In my ProBono application that uses SQLite to deal with persons, I suddenly have a question.

a. I set a New button to be able to add person(s). It clears the previous data and a click in the “Add” button add this new person. OK

b. It is so easy to “Delete” a card that I add a small window to avoid errors. OK.
c. I have a “Modify Card” button to be able to set a date for the person(s) that left the association (not for “employees”, for helped people). OK
d. An “Add” button add the current person. Usually to follow the “New”, then populated data. This adds a new persion in the data base.

I ask the association to make a backup at the end of december each year (export the data base to text and duplicate the .sqlite file). So, they have archival.
I also added a data base split (based on a set year), so they can remove too older years.

I am thinking that a History “log” can be useful. If I do that
a. how can I do it ?
b. what will I store there ?
Added persons (an added Record)
Modified data (a modified Record)
Deleted persons (a Record)

As usual, your input is welcome.

2

Normally I duplicate the tables and call them: xxx_<table_name>, without the foreign keys, constraints and primary key field (always an int auto) and add the following fields:

  • id: int auto PK
  • record_id: PK of original record
  • action: I, U, D
  • actioned_at: timestamp
  • actioned_by

And then on each operation I update these tables as well. Operations should of course be done in a transaction.

Usually I also provide a history button on forms so users can see the changes over time.

3

Thank you James for your input.

Actually, I do not attach a Date to the Record (say date of creation, then date of last change).

When I was writing the op, I was thinking that - at New, Modify, Duplicate, Delete, I can store a copy of the Record (prior the changes, so I have to read it from the .sqlite file - easy -) with the action date (to be sure this Record is unique and have the write date handly), and do what the user asked.

Then, I was feeling that this may lead into a very hudge .sqlite file.

Also, this file does not have a owner. I mean: anybody can Read (or Write). So, how useful storing an history log can be ?
And adding accounts privileges to the data base can be far byond the time I want to spend with this project.

At last, am I the only one to ask myself (and you) these (metaphysical) questions ?

They use the software since early 2013 (early 4 years). I recently decided to add some "error checkigs workaround what the user enters”. I added a master country name text file and fills a PopupMenu so they stop to have many entries with a slighty different spelled country name (!). I also filter leading / ending names (and remove duplicates spaces in the names), and so on.
But, every move in one direction may lead a different one by the user and if I restrain too much the access, the user(s) will stop… using the software.
At last, one part that decide me to adopt anti-error(s) preventive measures was because they are very reluctent to squash their own errors even when I told them how to search and found them (for example in reading the list of unique countries: if you have Abany and Albany, one of them miss a character :wink: …)

On the other hand, I may involve myself too much and concentrates only on the potential errors, spell checking, feature(s) to add… in the software.

Your opinion ?