SQL - 表设计 - DateCreated 和 DateUpdated 列
对于我的应用程序，有多个实体类，用户、客户、帖子等，
我即将设计数据库，并且我想存储创建和更新实体的日期。 这就是棘手的地方。 当然，一种选择是为每个实体表添加created_timestamp和update_timestamp列，但这不是多余的吗？
另一种可能性是创建一个存储此信息的日志表，并且可以使其包含对任何实体的更新的跟踪。
有什么想法吗？ 我倾向于实施后者。
For my application there are several entity classes, User, Customer, Post, and so on
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?
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我能想到的单日志表适用于所有表的方法有两个主要问题：
CreatedDate 和 ModifiedDate 列并不是多余的，因为它们是在每个表中定义的。 我会坚持这种方法，并在每个表上放置插入和更新触发器来填充这些列。 如果我还需要记录进行更改的最终用户，我将跳过触发器并从应用程序代码填充时间戳和用户字段。
The single-log-table-for-all-tables approach has two main problems that I can think of:
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.
我使用“日志”或“事件”表来执行后者。 根据我的经验，“更新”时间戳很快就会变得令人沮丧，因为很多时候您发现自己处于修复状态，而您不仅仅需要最新的更新时间。
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.
您需要多久将创建/更新的时间戳包含在表示层中？ 如果答案不仅仅是“很久一次”，我认为在每个表中包含这些列会更好。
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.
在我几年前参与的一个项目中，我们实现了触发器来更新我们所谓的审计表（它存储有关所做更改的基本信息，每个表一个审计表）。 这包括修改日期（和最后修改日期）。
它们仅应用于关键表（不适用于联接或引用数据表）。
这消除了许多通常需要考虑 LastCreated 和 LastCreated 的挫败感。 LastModified 字段，但引入了保持触发器最新的烦恼。
最后，触发器/审核表设计运行良好，我们只需记住在 ETL 之前删除并重新应用触发器（！）。
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.
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(!).
它适用于我正在开发的基于网络的 CMS。 创建和上次更新日期将显示在大多数页面上，并且将列出上次创建（和更新）页面。 管理界面也将使用此信息。
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.