Handling History tables in Dimensional Model
2 posters
Page 1 of 1
Handling History tables in Dimensional Model
Gurus,
We are designing dimensional model for our organization. In our current OLTP system we have base tables and history tables. The table structure of history tables is mirror of base tables with addition of 3 auditing columns (Transaction Type, Transaction User and Transaction Date). So I am wondering what should be our design approach. Does anyone have any thoughts on this? Thanks
Here is the sample table structure…
Base Table Structure:
BaseId (PK)
HistId (FK)
Col3
Col4
History Table Structure:
HistId (PK)
BaseId
Col3
Col4
TransactionType
TransactionUser
TransactionApp
TransactionUtc
We are designing dimensional model for our organization. In our current OLTP system we have base tables and history tables. The table structure of history tables is mirror of base tables with addition of 3 auditing columns (Transaction Type, Transaction User and Transaction Date). So I am wondering what should be our design approach. Does anyone have any thoughts on this? Thanks
Here is the sample table structure…
Base Table Structure:
BaseId (PK)
HistId (FK)
Col3
Col4
History Table Structure:
HistId (PK)
BaseId
Col3
Col4
TransactionType
TransactionUser
TransactionApp
TransactionUtc
kellog1- Posts : 4
Join date : 2009-12-19
Re: Handling History tables in Dimensional Model
Why do you have a HIST_ID FK in the base table? A general answer to your question is that all history is represented in the fact table.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Complex Dimensional Model Help - With History Product to Part
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Why we use Dimensional Model over De-normalized relational Model ?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Why we use Dimensional Model over De-normalized relational Model ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum