Should I store registration information in the main database table?

Question

For example, let's say I have a product table. Do I have to store the registration information, for example, who it was created, the last edited by the last updated date ... Or should I separate the registration information in the audit table if the registration information does not apply to the actual application?

Thanks.

Answer 1

If you are serious about storing audit information, it should go in a separate table. The latest updated material will simply be overwritten, this will not replace. If you add a timestamp to the key, you can save the history in the same table, but by increasing the number of queries and the application logic more confusing, so I would advise you to do this.

Answer 2

Typically, each table stores LastChangeUser and LastChangeDate, and sometimes CreateUser and CreateDate. This is usually suitable for most tables.

However, if you need to store more than that, for really important tables (usually money-related), go to another table. In this table (OriginalTableName_History) I usually have a history identifier, which is an automatic increment, history and historical type (I = insert, U = update, D = delete), and then all the columns from the source table. Usually I have one trigger on the main table that places each change (insert / update / delete) in the history table.

Answer 3

In short, it would be better to have in a separate table.

Answer 4

You should always separate your operational database (current information about products, customers, etc.) from the storage registration. Depending on the case, I also suggest that you create a history database and store all outdated data there so as not to have a redundant working database. Making choices in huge databases is always slow, so you should reduce its size by any means possible and create indexes to improve performance. Registration information should be stored in a different database. Fields such as "Last Modified By", I do not consider as information about registration, you can use them on any table that you want. I also suggest that you do not have too many foreign keys in the operating database (storing log information without a direct link to operational data), as this slows down data management.

Hope this helps.

Answer 5

I would save this separately, especially if you want to keep an audit trail. Entering registration into your organization means that you can have only one journal entry for each object. It also mixes individual issues — changes in the essence and essence itself represent different concepts and are best placed in separate tables.

In combo tables, deleting an object will then delete all audit information for that object, which may not be what you want.

Answer 6

It depends on your application and how much information you save. Some frameworks, such as Ruby on Rails, can automatically update fields such as created_by, so if you have this flexibility and you only need a few fields, it might be easier for you to just save them in a single table.

However, if you intend to record detailed information, for example, who changed the record at what time, it might be better to use a separate table. Thus, you can even keep a moving history of all changes made to the record for audit purposes.

Answer 7

Is the set of metadata that you register about every product that can grow? If so, put it in another table so you can add items.

If this is a specific set that will not grow, then it probably does not matter. You get a little conceptual elegance using a separate table, but you give up a little efficiency and complexity.

Answer 8

If your application needs to know only what the current attributes of the product are, it is advisable to put the audit information in another table, because it simplifies queries and improves performance.

On the other hand, if you need to reconstruct objects at certain points in time (for example, if your application usually should answer the question “Under which brand do we sell this product in 2004?”), You should never change records: changes to the object are part of the entity data and should be in the same table. (See Martin Fowler's article, “ Templates for Things that Change Over Time, ” for a good discussion of this in an object-oriented context.)