General database design - can I refer to field names in other tables

Question

I have a table with various fields that can be updated. Let it be called table1 and suppose that it has the following fields:

 user VARCHAR2 first_name VARCHAR2 last_name VARCHAR2 

My question is: I want to track changes in these fields. I have an application that allows me to update these fields, but I want to track when the field is being edited, who edited the field and which field was edited. This is the bit that I need help with.

I could have a history table:

 date_edited DATE who_edited VARCHAR2 field_name_edited VARCHAR2 

what if the field name has changed though? .. This would mean that field_name_edited would refer to a non-existent field. This seems like a stupid approach.

Should there be some general way to do such things?

Thank you very much.

EDIT

I am using Oracle DB - see new question tags.

Answer 1

"Should there be some general way to do such things?"

Not really, because your approach is general, but erroneous. Tracking changes at the column level is unsatisfactory for several reasons:

• This is expensive when an update touches multiple columns (inserting multiple audit records for each updated row).
• It is difficult to collect a consistent picture of the state of a record at a given point in time (software complex and more expensive to download).
• There is no elegant way to check if a given column has been modified.
• Saving a column value is random (implementations usually process different types of data by converting them to strings).

(Your proposed table does not concern this last point, which, I believe, is an oversight: a small value for the audit trail does not track the changed values).

So what is the general solution? Row level history tables. With triggers that insert the entire record into the log table (along with metadata columns such as DATE_EDITED and WHO_EDITED). These triggers can be easily created from a data dictionary.

It is true that this approach makes it difficult to determine which columns were edited in any transaction. But:

• difficult but not impossible. It can be implemented in SQL (using an analytic LAG or something else), but in reality the person is pretty good at detecting changes without any help.
• the cost is borne by the audit subsystem, which in real life - and no matter what the requirements say - will not be used much, and, of course, much less than the main part of the application. Therefore, it is better to offload processing costs in the audit subsystem, instead of making the log significant.

And, as I said, row-level hierarchization is less expensive than the column level, both when creating audit records and when receiving them.

---

In your comment, you are referring to an article about Total Recall product . This is actually a very elegant solution, which has very little effect on the main system and facilitates the restoration of the historical state of our tables. The problem is that before 12c it is optional for the Enterprise Edition, which makes it expensive. (In fact, it is now part of Advanced Compression , not a standalone product of its choice). In 12c, Basic Flashback Data Archive (a new name for Total Recall) is available in all releases , but we still need to buy the Advanced Compression Option to compress log tables.

---

"You recommend [save] row changes and thus save redundand data."

Yeah. These days, storage is usually cheap. The cost of storing redundant data is usually a good price for efficient recording and retrieval of records.

"You still need to copy the letter names of the source columns"

Journalling uses one audit table for each data table; The audit table follows the structure of the current table, and some additional columns contain metadata related to the transaction. We expect the audit columns to have the same names as their similar data columns. (Doing anything else would be stupid, not least because we can generate DDL for audit tables from a data dictionary.)

Answer 2

Disclaimer This is what I used for a similar situation a couple of years ago. I did not like it too much, with all the associations and triggers that we had to do. If someone has a better approach, write!

From what I understand, if you think the field names can change, you should have a master data table that tracks the field information. You will need to have one history table for the master data and one for the actual user table. As one of the posters said above, you can use triggers to write to the correct history table when one of the tables changes.

 table editable_fields id field_name created/modified_by/date 

sample entries = {(1, user), (2, first_name), (3, last_name)}

---

 table user id field1_value field2_value field3_value created/modified_by/date 

sample entries = {(1, johnsmith, John, Smith), (2, janedoe, Jane, Doe)}

---

 table user_fields_mapping id -- FKs to editable_fields.id field1_editable_id field2_editable_id field3_editable_id 

sample entries = {(1, 1, 2, 3), (2, 1, 2, 3), (3, 1, 2, 3)}