Design of history database

[Slaven Bojko]

Hi!

Please, tell mi if you know a good source (book, web page) about designing a
history database.

For example, I have an item that can be a member of group A till some date
and then a member of group B, after some other date it can be a meber of
group C or be back to group A. And so on... Of course, I have to keep a
track where some item was. In my design I have tree tables: items, groups,
and table whit links to items and groups whith date, so when some item
change group I plan to add a record whith a date of change and ID's of that
item and group.

Can someone tell me is that a good design?

Thanks and regards,

Slaven

(please remove the ".makniovo" from my e-mail address)

 

[SBD]

Depends on what you want to track. In the example you
give, I would have fields for Item ID, Date of change and
Current Group. This would mean creating a record when the
Item was first created as well.

 

[TC]

Sounds good to me. If the third table had these records:

ItemID GroupID DateEffective
11 22 3/4/2003
11 33 5/4/2003

that would mean that item 11 joined group 22 on 3/4/2003, then moved to
group 33 on 5/4/2003.

Of course, that assumes that an item can not be a member of several groups
at the same time. If it *could*, then, the example above would mean that
item 11 joined group 22 on 3/4/2003, then *also* joined group 33 on
5/4/2003.

HTH,
TC

 

[Slaven Bojko]

Well, thank you guys. Now all I have to do is to dig in those SQL statements
to make all the recordsets I need...