Re: [Q] Most logical way to keep history ?
From: Patrick Logan <patrickdlogan_at_home.com>
Date: 2000/07/30
Message-ID: <tmPg5.57678$V34.574608_at_news1.sttls1.wa.home.com>#1/1
> Are you really looking at "time-slices", i.e. state at time t? This
> leans towards one table, but note that your relationships get more
> complicated, e,g 1-n becomes m-n. Your primary and foreign keys
> expand to include timestamps, so consider a guid primary key with a
> separate table relating this key to the time/version info instead.
Date: 2000/07/30
Message-ID: <tmPg5.57678$V34.574608_at_news1.sttls1.wa.home.com>#1/1
Richard MacDonald <macdonaldrj_at_worldnet.att.net> wrote in message news:_Yqg5.12548$RG6.1036765_at_bgtnsc05-news.ops.worldnet.att.net...
>
> Are you really looking at "time-slices", i.e. state at time t? This
> leans towards one table, but note that your relationships get more
> complicated, e,g 1-n becomes m-n. Your primary and foreign keys
> expand to include timestamps, so consider a guid primary key with a
> separate table relating this key to the time/version info instead.
An earlier post had a useful solution along these lines, i.e. a star schema. Create a "factless fact table" with surrogate keys to the time dimensions (begin and end time) and to the employee table, the location table, etc.
Location can be a "Type II Slowly Changing Dimension". When the employee changes locations, add a new "fact" that refers to the new dates and the new location, but the same employee.
-- Patrick Logan mailto:patrickdlogan_at_home.com http://www.pssg.comReceived on Sun Jul 30 2000 - 00:00:00 CEST