Just a generic question.
Do you guys add audit columns to all your db tables?
Columns like created_on, created_by, modified_on and modified_by.
I tend to do it even if the project is small.
Do you have other tables that accompany you through all your projects? Do you have your own DB framework? i.e I always use a table called status that holds all possible statuses of records. (active, marked_as_deleted, inactive, etc)
I tend to have these 9 columns at the end of every MySQL table, whether they are needed/used or not.
Most are self explanatory, but for the ones that aren’t…
I have always tussled with the idea of having this in a separate table but keep coming back to the idea of having less, not more, data connections.
The one thing I really want to do but never get round to setting up is a change log. I can’t decide the best way to do it and what info to hold.
Nice! I might steal something from there 
For change log a well known approach is to have duplicated tables something like cars and cars_log. Every time you update a record you copy the current row to the cars_log and leave cars always with the last version. This avoids querying huge amounts of data when it’s not needed.
When showing audit data you query the log table and when showing data to end user you query the normal table.
With this approach I see some synergies with your data_version column.
I guess the best option in mysql would be to use BEFORE/AFTER server triggers. I haven’t implemented logging either so this is just a wild guess.
Why don’t you have as default value for data_created CURRENT_TIMESTAMP?
Steal away 
This particular DB is hosted on my clients server and they have MariaDB (Server version: 5.5.64-MariaDB) which only allows one instance of CURRENT_TIMESTAMP. So to get around this I use {{NOW_UTC.dateAdd(“hours”, 1)}} in each DB INSERT. Obviously not needed in the UPDATE.
I use modified_by and modified_date on every table I use.
With modified _by, I use Wappler’s security provider identity to insert the user id of whomever modified the table row.
With modified_date, I use the Wappler data picker and insert NOW for the date of the change or insert.
For the data audits, I have before insert, before update, and before delete triggers on all my tables.
The one thing I have not gotten around to as of yet is how to revert the data back to an earlier form.
when the wappler has worked with migrations this will be a breeze
Migrations normally affect schemas, not data content.
I was faced with the changelog topic a couple weeks ago for a internal corporate app. I was really bouncing back and forth between “paired” tables or single with column indicators. Pros and cons to both.
I went with indicators in a single table on this one. I basically do inserts for everything and modify a status field along with valid_from valid_to timestamp fields to clearly show when this data was active. This is actually displayed to certain users that need to see changes to records… They can just step through versions of the record to see the change history.
There are some cool ideas shared above! Nice thread.
SAP R/3 uses a similar approach.
Regarding versioning. Check this out for mariadb:
It looks promising.
You need to add row_start and row_end to the SELECT statement as they are system columns generated by the versioning system.
@mebeingken I’ve been looking deeper into mariadb versioning system and it seems you can have best of both worlds via table partitions.
In many systems I work with. This is the minimum standard, along with an ID field (integer or UUID).
I also prefer to add a boolean “active” field and use it as a condition in most queries so it is easy to not display data.
I also like to use a table to store the apps menu values. If building a SaaS, you can also store custom values that each account/user creates in the same table by using a column that relates their Account vs Global/System values. If I’m building multiple modules (e.g. task module, contact module, etc) then I add a column to differentiate.
Thanks Jon…I will have to check that out!
Would writing every edit to both cars and cars_log do what you are thinking? The log file would always contain the most recent version before any future edit.
I’ve also been considering creating tables called sandboxes. The idea was to run whatever queries were needed and write the results into bonus_sb. Having reviewed the results then commit the results to the bonus table. The thought was to give me a chance to correct errors prior to them being committed to the database.
If you mean directly from the app that would probably be overkill although it’s an option of course. You probably want to move that to the database layer using an BEFORE/AFTER update trigger so it happens in the backend and doesn’t affect UX.
Also if you add it to the database layer you only have to do it once and forget.
