mysql - 将元数据记录为实体表的一部分还是单独的？

Question

对于我的应用程序中的实体，我计划记录常见的元数据，例如 DateCreated、DateModified、IPAddress 等。在实体表中添加这些列是否有意义，还是使用单个表来记录元数据更好-数据，其中每行都有链接回其对应的项目？稍后为了报告和分析的目的，我可以创建所需的 View 。

Answer 1

我使用一个特殊的查询将所有这些公共(public)列(我称之为审计列)添加到所有表(使用游标遍历信息模式)，这使得将它们应用到新数据库变得很容易。

我的列是(特定于 SQL Server):

RowId UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()),Created DATETIME NOT NULL DEFAULT (GETDATE()),Creator NVARCHAR(256) NOT NULL DEFAULT(SUSER_SNAME())RowStamp TIMESTAMP NOT NULL

现在，在完全规范化的架构中，我只需要 RowId，它将链接到包含其他行的审核表。事实上，经过反射(reflection)，我几乎希望我已经走这条路 - 主要是因为它使表变得丑陋(事实上，我将这些列从数据库模式图中排除)。

但是，在处理非常大的数据集时，您确实可以通过在表中包含这些数据来提高性能，而且迄今为止我在使用该系统时还没有遇到任何问题。

编辑:不妨发布代码来添加审核列:

DECLARE AuditCursor CURSOR FOR  SELECT TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE'  AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0  AND TABLE_NAME NOT IN ('sysdiagrams')  AND TABLE_NAME NOT LIKE 'dt_%'  -- NB: you could change the above to only do it for certain tablesOPEN AuditCursor  DECLARE @schema varchar(128), @table varchar(128)  FETCH NEXT FROM AuditCursor INTO @schema, @table  WHILE @@FETCH_STATUS  -1  BEGIN    PRINT '* Adding audit columns to [' + @schema + '].[' + @table + ']...'    IF NOT EXISTS  (SELECT NULL        FROM information_schema.columns        WHERE table_schema = @schema        AND table_name = @table        AND column_name = 'Created')    BEGIN      DECLARE @sql_created varchar(max)      SELECT  @sql_created = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Created] DATETIME NOT NULL CONSTRAINT [DF_' + @table + '_Created] DEFAULT (GETDATE())'      EXEC  ( @sql_created )      PRINT ' - Added Created'    END    ELSE      PRINT ' - Created already exists, skipping'    IF NOT EXISTS  (SELECT NULL        FROM information_schema.columns        WHERE table_schema = @schema        AND table_name = @table        AND column_name = 'Creator')    BEGIN      DECLARE @sql_creator varchar(max)      SELECT  @sql_creator = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Creator] VARCHAR(256) NOT NULL CONSTRAINT [DF_' + @table + '_Creator] DEFAULT (SUSER_SNAME())'      EXEC  ( @sql_creator )      PRINT ' - Added Creator'    END    ELSE      PRINT ' - Creator already exists, skipping'    IF NOT EXISTS  (SELECT NULL        FROM information_schema.columns        WHERE table_schema = @schema        AND table_name = @table        AND column_name = 'RowId')    BEGIN      DECLARE @sql_rowid varchar(max)      SELECT  @sql_rowid = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @table + '_RowId] DEFAULT (NEWID())'      EXEC  ( @sql_rowid )      PRINT ' - Added RowId'    END    ELSE      PRINT ' - RowId already exists, skipping'    IF NOT EXISTS  (SELECT NULL        FROM information_schema.columns        WHERE table_schema = @schema        AND table_name = @table        AND (column_name = 'RowStamp' OR data_type = 'timestamp'))    BEGIN      DECLARE @sql_rowstamp varchar(max)      SELECT  @sql_rowstamp = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowStamp] ROWVERSION NOT NULL'      EXEC  ( @sql_rowstamp )      PRINT ' - Added RowStamp'    END    ELSE      PRINT ' - RowStamp or another timestamp already exists, skipping'    -- basic tamper protection against non-SA users    PRINT ' - setting DENY permission on audit columns'    DECLARE @sql_deny VARCHAR(1000)    SELECT  @sql_deny = 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Created]) TO [public]'      + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([RowId]) TO [public]'      + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Creator]) TO [public]'    EXEC (@sql_deny)    PRINT '* Completed processing [' + @schema + '].[' + @table + ']'    FETCH NEXT FROM AuditCursor INTO @schema, @table  ENDCLOSE AuditCursorDEALLOCATE AuditCursorGO