All geek questions in one place

SQL-Audit log table design - Which would you prefer?

For the project in which my company works, we need an audit (storing logs) for various tasks (changes made to tables in the system) that users perform. Currently, there are only three different types of tasks. But it can grow in the future.

My suggestion for this was the following schema table and relationships (example):

Table AuditLog
------------------------------
Id | PK
Description
Created

And for each task:

Table ExampleTaskAuditLog
------------------------------
ExampleTaskId | FK PK
AuditLogId | FK PK

and

Table AnotherExampleTaskAuditLog
------------------------------
AnotherExampleTaskId | FK PK
AuditLogId | FK PK

In principle, for each task we must conduct an audit, we will have a new table containing the relationship.

What another developer suggested was the following:

Table AuditLog
------------------------------
Id (PK)
Description
Created
ExampleTaskId | NULLABLE
AnotherExampleTaskId | NULLABLE
Type | (an integer id which indicates whether this is a "example task" or a "another example task").

Basically, if we created a log for "ExampleTask", we would set the ExampleTaskId field to the example task identifier and Type for the corresponding ExampleTask-enum value.

, (, , !) . - FK, , (, RMDBS-MSSQL). , , ( ..). , . . , . , , , , 5-10K . 30-40K , .

? , , ?

+3
2

, - ExampleTaskId AnotherExampleTaskId - , ?

  • Id
  • TaskId
  • TaskType

, AuditLog TaskType, .

, ( ) (.. , ), (, ). :

Table ExampleTaskAuditLog
------------------------------
AuditId (PK)
TaskSpecificField
AnotherTaskSpecificField
+2

, //, , , @ (). , .

: ? () , . ? ( "" " , ", "" , , -.)

0

Source: https://habr.com/ru/post/1793040/

More articles:


All Articles