I am designing a database for a site which will be heavily datadriven with forums and chat and other community features.
我正在为一个网站设计一个数据库,该网站将通过论坛和聊天以及其他社区功能进行大量数据开发。
My question is this: I have identified certain information I would like to store for at least most of the tables. Creation date, last update, creator and others.
我的问题是:我已经确定了我想为至少大多数表存储的某些信息。创建日期,最后更新,创建者和其他人。
Which is the best way to store these, in a dedicated table or as fields in each respective table?
哪个是存储这些的最佳方式,在专用表中或作为每个表中的字段?
Why is the better solution better? Can the opinion of best solution change if I have 50 tables or 100 tables? 100 000 records vs 4 million?
为什么更好的解决方案更好?如果我有50张桌子或100张桌子,最佳解决方案的意见会改变吗? 10万条记录对400万条记录?
Are there other aspects?
还有其他方面吗?
1 个解决方案
#1
2
Creation date and last update date are best stored as columns in the same table with their respective data. By doing that, you can use the TIMESTAMP data type so they are updated automatically when their respective rows change.
创建日期和上次更新日期最好存储为同一表中的列及其各自的数据。通过这样做,您可以使用TIMESTAMP数据类型,以便在各自的行更改时自动更新它们。
The NULL timestamp on the row_created column will default to the timestamp when the row is created. In row_updated, the timestamp will be updated automatically every time the row is modified in any way. You do not need to modify them in application code.
row_created列上的NULL时间戳将默认为创建行时的时间戳。在row_updated中,每次以任何方式修改行时,时间戳都将自动更新。您无需在应用程序代码中修改它们。
CREATE TABLE test.table (
row_created TIMESTAMP NULL,
row_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
The creator user isn't as critical to store in the same table, but it doesn't really hurt to do it in the same table or another. For simplicity I would keep it in the same table.
创建者用户并不像在同一个表中存储那么重要,但在同一个表或另一个表中执行它并不会造成太大的伤害。为简单起见,我会将它保存在同一个表中。
#1
2
Creation date and last update date are best stored as columns in the same table with their respective data. By doing that, you can use the TIMESTAMP data type so they are updated automatically when their respective rows change.
创建日期和上次更新日期最好存储为同一表中的列及其各自的数据。通过这样做,您可以使用TIMESTAMP数据类型,以便在各自的行更改时自动更新它们。
The NULL timestamp on the row_created column will default to the timestamp when the row is created. In row_updated, the timestamp will be updated automatically every time the row is modified in any way. You do not need to modify them in application code.
row_created列上的NULL时间戳将默认为创建行时的时间戳。在row_updated中,每次以任何方式修改行时,时间戳都将自动更新。您无需在应用程序代码中修改它们。
CREATE TABLE test.table (
row_created TIMESTAMP NULL,
row_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
The creator user isn't as critical to store in the same table, but it doesn't really hurt to do it in the same table or another. For simplicity I would keep it in the same table.
创建者用户并不像在同一个表中存储那么重要,但在同一个表或另一个表中执行它并不会造成太大的伤害。为简单起见,我会将它保存在同一个表中。