For my application there are several entity classes, User, Customer, Post, and so on
对于我的应用程序,有几个实体类,User,Customer,Post等
I'm about to design the database and I want to store the date when the entities were created and updated. This is where it gets tricky. Sure one option is to add created_timestamp and update_timestamp columns for each of the entity tables but that isn't that redudant?
我即将设计数据库,我想存储创建和更新实体的日期。这是它变得棘手的地方。当然有一个选项是为每个实体表添加created_timestamp和update_timestamp列但不是那个还原剂?
Another possibility could be to create a log table that stores this information, and it could be made to contain keep track of updates for any entity.
另一种可能性是创建一个存储此信息的日志表,并且可以使其包含对任何实体的更新的跟踪。
Any thoughts? I'm leaning on implementing the latter.
有什么想法吗?我倾向于实施后者。
5 个解决方案
#1
6
The single-log-table-for-all-tables approach has two main problems that I can think of:
单对数表对所有表的方法有两个主要问题,我可以想到:
- The design of the log table will (probably) constrain the design of all the other tables. Most likely the log table would have one column named TableName and then another column named PKValue (which would store the primary key value for the record you're logging). If some of your tables have compound primary keys (i.e. more than one column), then the design of your log table would have to account for this (probably by having columns like PKValue1, PKValue2 etc.).
- If this is a web application of some sort, then the user identity that would be available from a trigger would be the application's account, instead of the ID of the web app user (which is most likely what you really want to store in your CreatedBy field). This would only help you distinguish between records created by your web app code and records created otherwise.
日志表的设计(可能)会限制所有其他表的设计。很可能日志表将有一个名为TableName的列,然后是另一个名为PKValue的列(它将存储您正在记录的记录的主键值)。如果您的某些表具有复合主键(即多个列),则日志表的设计必须考虑到这一点(可能通过使用PKValue1,PKValue2等列)。
如果这是某种Web应用程序,则触发器可用的用户身份将是应用程序的帐户,而不是Web应用程序用户的ID(这很可能是您真正想要存储在CreatedBy中的ID)领域)。这只会帮助您区分Web应用程序代码创建的记录和其他创建的记录。
CreatedDate and ModifiedDate columns aren't redundant just because they're defined in each table. I would stick with that approach and put insert and update triggers on each table to populate those columns. If I also needed to record the end-user who made the change, I would skip the triggers and populate the timestamp and user fields from my application code.
CreatedDate和ModifiedDate列不是冗余的,因为它们是在每个表中定义的。我会坚持使用这种方法,并在每个表上放置插入和更新触发器来填充这些列。如果我还需要记录进行更改的最终用户,我会跳过触发器并从我的应用程序代码中填充时间戳和用户字段。
#2
5
I do the latter, with a "log" or "events" table. In my experience, the "updated" timestamp becomes frustrating pretty quick, because a lot of the time you find yourself in a fix where you want not just the very latest update time.
我使用“日志”或“事件”表来执行后者。根据我的经验,“更新”的时间戳很快就变得令人沮丧,因为很多时候你发现自己处于修复中,而不仅仅是最新的更新时间。
#3
0
How often will you need to include the created/updated timestamps in your presentation layer? If the answer is anything more than "once in a great great while", I think you would be better served by having those columns in each table.
您需要多长时间在表示层中包含已创建/更新的时间戳?如果答案不仅仅是“曾经在很长一段时间内”,那么我认为通过在每个表中添加这些列可以提供更好的服务。
#4
0
On a project I worked on a couple of years ago, we implemented triggers which updated what we called an audit table (it stored basic information about the changes being made, one audit table per table). This included modified date (and last modified).
在几年前我工作过的一个项目中,我们实现了触发器,它们更新了我们称之为审计表的内容(它存储了有关正在进行的更改的基本信息,每个表有一个审计表)。这包括修改日期(和最后修改)。
They were only applied to key tables (not joins or reference data tables).
它们仅应用于关键表(不是连接或引用数据表)。
This removed a lot of the normal frustration of having to account for LastCreated & LastModified fields, but introduced the annoyance of keeping the triggers up to date.
这消除了不得不考虑LastCreated和LastModified字段的许多正常挫折,但引入了使触发器保持最新的烦恼。
In the end the trigger/audit table design worked well and all we had to remember was to remove and reapply the triggers before ETL(!).
最后,触发/审计表设计运行良好,我们必须记住的是在ETL(!)之前删除并重新应用触发器。
#5
0
It's for a web based CMS I work on. The creation and last updated dates will be displayed on most pages and there will be lists for the last created (and updated) pages. The admin interface will also use this information.
这是我工作的基于Web的CMS。创建和上次更新日期将显示在大多数页面上,并且将显示最后创建(和更新)页面的列表。管理界面也将使用此信息。
#1
6
The single-log-table-for-all-tables approach has two main problems that I can think of:
单对数表对所有表的方法有两个主要问题,我可以想到:
- The design of the log table will (probably) constrain the design of all the other tables. Most likely the log table would have one column named TableName and then another column named PKValue (which would store the primary key value for the record you're logging). If some of your tables have compound primary keys (i.e. more than one column), then the design of your log table would have to account for this (probably by having columns like PKValue1, PKValue2 etc.).
- If this is a web application of some sort, then the user identity that would be available from a trigger would be the application's account, instead of the ID of the web app user (which is most likely what you really want to store in your CreatedBy field). This would only help you distinguish between records created by your web app code and records created otherwise.
日志表的设计(可能)会限制所有其他表的设计。很可能日志表将有一个名为TableName的列,然后是另一个名为PKValue的列(它将存储您正在记录的记录的主键值)。如果您的某些表具有复合主键(即多个列),则日志表的设计必须考虑到这一点(可能通过使用PKValue1,PKValue2等列)。
如果这是某种Web应用程序,则触发器可用的用户身份将是应用程序的帐户,而不是Web应用程序用户的ID(这很可能是您真正想要存储在CreatedBy中的ID)领域)。这只会帮助您区分Web应用程序代码创建的记录和其他创建的记录。
CreatedDate and ModifiedDate columns aren't redundant just because they're defined in each table. I would stick with that approach and put insert and update triggers on each table to populate those columns. If I also needed to record the end-user who made the change, I would skip the triggers and populate the timestamp and user fields from my application code.
CreatedDate和ModifiedDate列不是冗余的,因为它们是在每个表中定义的。我会坚持使用这种方法,并在每个表上放置插入和更新触发器来填充这些列。如果我还需要记录进行更改的最终用户,我会跳过触发器并从我的应用程序代码中填充时间戳和用户字段。
#2
5
I do the latter, with a "log" or "events" table. In my experience, the "updated" timestamp becomes frustrating pretty quick, because a lot of the time you find yourself in a fix where you want not just the very latest update time.
我使用“日志”或“事件”表来执行后者。根据我的经验,“更新”的时间戳很快就变得令人沮丧,因为很多时候你发现自己处于修复中,而不仅仅是最新的更新时间。
#3
0
How often will you need to include the created/updated timestamps in your presentation layer? If the answer is anything more than "once in a great great while", I think you would be better served by having those columns in each table.
您需要多长时间在表示层中包含已创建/更新的时间戳?如果答案不仅仅是“曾经在很长一段时间内”,那么我认为通过在每个表中添加这些列可以提供更好的服务。
#4
0
On a project I worked on a couple of years ago, we implemented triggers which updated what we called an audit table (it stored basic information about the changes being made, one audit table per table). This included modified date (and last modified).
在几年前我工作过的一个项目中,我们实现了触发器,它们更新了我们称之为审计表的内容(它存储了有关正在进行的更改的基本信息,每个表有一个审计表)。这包括修改日期(和最后修改)。
They were only applied to key tables (not joins or reference data tables).
它们仅应用于关键表(不是连接或引用数据表)。
This removed a lot of the normal frustration of having to account for LastCreated & LastModified fields, but introduced the annoyance of keeping the triggers up to date.
这消除了不得不考虑LastCreated和LastModified字段的许多正常挫折,但引入了使触发器保持最新的烦恼。
In the end the trigger/audit table design worked well and all we had to remember was to remove and reapply the triggers before ETL(!).
最后,触发/审计表设计运行良好,我们必须记住的是在ETL(!)之前删除并重新应用触发器。
#5
0
It's for a web based CMS I work on. The creation and last updated dates will be displayed on most pages and there will be lists for the last created (and updated) pages. The admin interface will also use this information.
这是我工作的基于Web的CMS。创建和上次更新日期将显示在大多数页面上,并且将显示最后创建(和更新)页面的列表。管理界面也将使用此信息。