One log table versus log columns in each table

Question

I enter insert and update information in each table

create_date TIMESTAMP create_user_id INT update_date TIMESTAMP update_user_id INT 

I thought that instead of putting them in each table, creating only one log table and referring to the log table on each table, so that I can only get log information when I need to. I know this depends on the application (I am developing a small commercial application like ERP), but do you have any experience with this type of table? any performance issues, maintenance? Which do you prefer? Thanks.

 log_id LONG create_date TIMESTAMP create_user_id INT update_date TIMESTAMP update_user_id INT 

Edit: I came to the conclusion that using only update_date (the insert will be considered as updating, not deleting data, but simply inactivating) and update_user_id columns for each table. I use MySQL, and there will be master and slave servers in the production environment. I will use replication logs on a slave server to check for data changes if there is any situation with a data countdown, so the design is simpler and the audit capability is possible, although it's not easy.

Answer 1

A single log table can easily become a bottleneck if it is written in addition to any other record.

You will also create an additional JOIN for some queries.

In my opinion, I don’t see the advantages of a separate table, except that the rest of the database tables are a bit “cleaner”

Answer 2

One magazine is a wonderful thing.

Each table has a log-only identifier column. Call it LOG_ID or something like that.

Anytime you do an INSERT, UPDATE, or DELETE, it works as follows.

• INSERT the log entry, get the assigned LOG_ID.

• Do INSERT or UPDATE by setting the foreign key LOG_ID in the modified line. For DELETE, you have two options: actually delete the line or mark the line as "inactive", but not actually delete it. This second choice makes your log of all changes completely complete, but makes your tables quite large and slow due to inactive rows that need to be skipped.

• Commit.

Make sure your journal project may include the following types of information.

• Change the database row (insert, update, delete). Insert and update changes will have an FK link to the changed line somewhere. Be sure to include the name of the table so that the application program can correctly find the table. To delete the changes, only the table name will be indicated.

• Other processing data, such as batch jobs. In this way, you can record the start / stop of periodic start-up and runtime and save the complete processing history.

Answer 3

About 20 years ago, I learned that the best way to handle this information is to simply paste it into a database. You should not modify existing entries and specifically do not delete entries. Basically, what you will store is not the data itself, but the data modifications. As a result, you simply get a single table that could do everything you need if you had enough processor / disk speed to view all the data in order to calculate the data based on these inserts.

Saving only changes will allow you to keep a complete history of all your data, which allows you to be extremely flexible. Basically, you only need one table containing everything. The disadvantage is that you need to do a lot more computation and need a lot more reading on disk, so you need tricks to speed up the process. Flexibility through productivity ...

In this case, you will have a similar problem. Access to a single table can be obtained from all that would need to add or update records. This will be your bottleneck. It is still great in a single-user environment since only one user will use it. In a low-maintenance environment, it can still have very good performance. But if you have 250 users who are constantly accessing this table, this will reduce overall performance.

In addition, you will find a maintenance problem when someone deletes entries. In these cases, you also need to delete entries in this log table.

It brings me back to what I mentioned at the beginning ... What you can do is combine the history table and regular tables. Whenever a record is inserted, changed, or deleted, you will add a record to this change table, including a time stamp and a user link. This will save the history according to your data, and in general you will only do inserts into this table. It should be fast. As an added bonus, you can recreate the entire contents of your database by simply replaying all the actions inside this change table just in case.

Inserts can be reasonable quickly, so the loss of performance is reasonably small, although it depends on your implementation.

But for some reason, learning about the use of modification tables, I never came into a practical situation where I could just use this technique.

Answer 4

we usually use them in most tables:

 LastChgID int LastChgDate datetime 

Sometimes it will be used, this is on several:

 CreateID int CreateDate datetime LastChgID int LastChgDate datetime 

others have a complete mirror table where we register each column with a flag such as change type, time date, and user ID.

I would stay away from the table that you update all the time, just add the columns, for which the database is intended, for storing information. We had a table that was enlarged (with an update) every time a stored procedure was called, and it was a blocking magnet.

We have a common log table, but it is only an insert and contains information about debugging and error. It is not updated every time any line changes, only when the developer decided to write something there, do not forget to specify several header fields (identifier, datetime, user identifier, called procedure) with a long formatted string containing this message.