Denormalising a fully normalised table

Question

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.

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.

eg.

TableA fields:

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

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>

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.

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

eg.

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...

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.

Which DBMS are you using? – Daniel Hilgarth Mar 19 '13 at 12:38 — Daniel Hilgarth, Mar 19 '13 at 12:38

score 2 · Answer 1 · answered Mar 19 '13 at 12:46

2

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.

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

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.

answered Mar 19 '13 at 12:46

Guffa

687,336
108
737
1,005

Hmm yes I take your point, unfortunately I'm not at liberty to change the schema. – atamata Mar 19 '13 at 13:54
@atamata: I see, I thought that you could do that, because that's what you do when you denormalise a table. Then you are in for the complicated road... at least you know what's making it complicated. – Guffa Mar 19 '13 at 14:04

score 1 · Answer 2 · answered Mar 19 '13 at 13:56

If you want the most recent records from the history table, you can use the following SQL:

select ht.*
from (select ht.*,
             ROW_NUMBER() over (partition by FieldChanged, TableA_Id order by DateCreated desc) as seqnum
      from HistoryOfTableA ht
      where datecreated <= YOURDATEHERE
     ) ht
where seqnum = 1

If you want this as a single record, you should either pivot or do an aggregation. Here is the latter approach:

select ht.TableA_id,
       max(case when FieldName = 'FieldA' then NewValue end) as FieldA,
       . . .
from (select ht.*,
             ROW_NUMBER() over (partition by FieldChanged, TableA_Id order by DateCreated desc) as seqnum
      from HistoryOfTableA ht
      where datecreated <= YOURDATEHERE
     ) ht
where seqnum = 1
group by TableA_id

Thanks, that's helpful and I will actually use it for some of my other reporting. — atamata, Mar 20 '13 at 14:23

score -1 · Answer 3 · answered Mar 19 '13 at 12:38

-1

You can try using a XML column in the TableA to record the history of every row in TableA. i.e. Every row has a column which is of type XML and records the history of that row. Then you can pull the rows into your application(in a batched manner if the number of rows are huge), and find out the history of each record in TableA

answered Mar 19 '13 at 12:38

guruprasath

277
3
17

1

This sounds like a bad idea. XML in a column to store history? How do you suppose an update would look like? – Daniel Hilgarth Mar 19 '13 at 12:39
Hi Daniel, Every update stored procedure can record the history in that column by 1) taking a snapshot of the record before the update happens, or 2) record only the changed fields because we know while updating which fields will change. Can you elaborate more on why this is a bad idea? – guruprasath Mar 19 '13 at 12:42
For every update, you would have to (1) parse the XML, (2) append the new history and (3) persist the XML. Furthermore, that history now no longer is accessible via SQL. – Daniel Hilgarth Mar 19 '13 at 12:47
Agreed that the parsing portion is an overhead. But there are legitimate design patterns which read and then update the data. Points 2 and 3 naturally follow in that pattern of read & persist. Again, there are DBMS technologies which allow XML querying. – guruprasath Mar 19 '13 at 12:50

Denormalising a fully normalised table

3 Answers3

Linked