对完全规范化的表进行反规范化 [英] Denormalising a fully normalised table

问题描述

谁能就如何对完全规范化的表进行非规范化提出建议?尽管在谷歌上搜索，我还是没能找到太多内容.

Can anyone advise on how to go about denormalising a fully normalised table? I've not been able to find much on it despite googling.

每当在 TableA 中更新记录(虽然没有插入，但现在不用担心)时，都会将记录插入到 HistoryOfTableA 中，其中包含已更改的字段值以及执行时关联的时间戳.

Whenever a record is updated (though not inserted, but lets not worry about that now) in TableA a record is inserted into HistoryOfTableA with the values of the fields which have changed and an associated timestamp when it was carried out.

例如

表A字段:

TableA_Id, FieldA, FieldB, FieldC, FieldD.... etc

HistoryOfTableA 记录:

HistoryOfTableA records:

HistID, TableA_Id, FieldChanged, OldValue, NewValue, DateCreated
1, 1, 'FieldA', 1, 2, <2013-03-18 12:20:00>
2, 1, 'FieldB', A, B, <2013-03-18 12:20:00>
3, 1, 'FieldC', A, B, <2013-03-18 12:20:00>

情况是我希望创建一些用于报告目的的 SQL.因此，我希望能够指定一个时间点，并且能够将这些主机表条目汇总在一起，并计算出 TableA 中该记录当时的状态.

The situation is I'm looking to create some SQL for reporting purposes. So I want to be able to specify a point in time, and be able pull together those hostory table entries and work out what the state of that record from TableA was at that time.

我想我可以根据历史表创建表，并在创建日期和表 A 中的 Id 上加入

I'm thinking I can create tables based on the history table and joined on the date created and the Id from table A

例如

select HistA.NewValue, 
       HistB.NewValue, 
       .... 
from FieldA_HistoryOfTableA HistA 
     inner join FieldB_HistoryOfTableA HistB on HistA.DateCreated = HistB.DateCreated
       and HistA.TableA_Id = HistB.TableA_Id 
     inner join ... etc
where HistA.FieldChanged = 'FieldA'
and HistB.FieldChanged = 'FieldB'
and .... etc...

但我认为这不会给我所有我想要的东西，而且我可能无法纯粹用 SQL 来做到这一点.顺便说一句，TableA 中有 20 个字段，因此尝试连接 20 个表可能并不明智.

But I don't think this is going to give me all that I want and I may not be able to do this purely in SQL. As an aside there are 20 fields in TableA so trying to join 20 tables may not be wise.

推荐答案

您的问题不在于表格已标准化，而在于它没有标准化.

Your problem is not that the table is normalised, but rather that it isn't.

一个表中字段标识的数据被另一个表中的数据标识，查询起来非常复杂.

The data identified by fields in one table is identified by data in another table, which makes it very complicated to query.

完全规范化的版本还会将 TableA 中的字段存储在单独的表中:

A fully normalised version would also store the fields from TableA in a separate table:

TableA
------------
TableA_Id

TableAFields
-------------
TableA_Id
FieldId
Value

Field
---------
FieldId
FieldName

HistoryOfTableA
----------------
TableA_Id
FieldId
OldValue
ChangedDate

现在您可以加入历史表中的字段.这会有点棘手，但至少它不会是一个有 20 个连接的查询.

Now you can join in the fields from the history table. That will be a bit tricky, but at least it won't be a query with 20 joins.

这篇关于对完全规范化的表进行反规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！