Handling History tables in Dimensional Model

View previous topic View next topic Go down

Handling History tables in Dimensional Model

Post  kellog1 on Sat Dec 19, 2009 6:40 pm

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

kellog1

Posts : 4
Join date : 2009-12-19

View user profile

Back to top Go down

Re: Handling History tables in Dimensional Model

Post  BoxesAndLines on Sun Dec 20, 2009 3:01 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum