While focusing on a cms, I have hit a little of the wall. Returning to my data model, I have observed some problems that can be more widespread as time passes.
Namely, I wish to conserve a audit trail (change log) of record modification by user (even user record modifications could be drenched). Because of the inclusion of the arbitrary quantity of modules, I am unable to make use of a by-table auto incrementation area for initially my secrets, because it will in the end cause conflicts while trying to keep their secrets in one table.
The audit trail would keep records of
action (Place/UPDATE/Remove), and
archive (a serialized copy from the old record)
I have considered a couple of possible methods to the problem, for example producing a
UUID primary type in application logic (to make sure mix database platform compatibility).
An alternative choice I have considered (and I am sure the consensus is going to be negative for thinking about this process) is, developing a
RecordKey table, to keep a globally auto-incremented key. However, I am sure you will find much better techniques to do this.
Ultimately, I am curious to understand of the items options I ought to look into trying to implement this. For instance, I anticipate enabling (to begin a minimum of) choices for MySQL and SQLite3 storage, but I am worried about how each database would handle
Edit to create my question less vague: Would using global IDs be considered a suggested solution for my problem? If that's the case, utilizing a 128 bit UUID (application or database produced) so what can I actually do during my table design that will help maximize query efficiency?
What about keeping all of the
record_id close to each table, and adding another column
table_name (towards the audit table) to create an amalgamated key?
By doing this you may also easily filter your audit log by
table_name (which is tricky with arbitrary UUID or sequence amounts). So even when you don't opt for this solution, attempt to add the
table_name column anyway with regard to querying the log later.
To be able to fit the
record_id all tables in to the same column, you'd still have to enforce that tables make use of the same data type for his or her ids (however it appears as if you were planning to achieve that anyway).
A far more effective plan would be to create an audit table that mirrors the dwelling of every table instead of invest the audit trail into one place. The "shadow" table model causes it to be simpler to question the audit trail.