Re: History tracking in databases

I agree with the Crate-->CurrentBox-->OldBox relationship. I would suggest a 'Group' field to keep these boxes into one historical group if all the box fields are not static. This 'Group' field should be managed by the program only. Try the following relationship: Crate(CrateNo)-->Box(Group)-->OldBox But wouldn't it be better to combine old box and new box data into one file for easier handling. Why the overhead of one more file? The 'Revision' and 'Active' fields are goods ideas for keeping boxes in one file. Using 'Revision','Active' and 'Group' fields together would be a good direction. *Have a nice day* :-) Gerard H. Pille wrote: > I don't think the "old box" is a box at all, it's historical > information > about a box. So, I'd have three entities: Crate, Box, Box_history. A > Box > would reference a Crate, A Box_history would of course reference its > box. > Don't mix 'history' with 'active' data, it complicates matters > enormously. > -- > > > > > Say you have a CRATE (CRATE1) which can contain > > smaller BOXes (BOX1,BOX2 > > etc.) > > > > CRATE1 is related to BOX1 and BOX2. > > > > Each BOX contains information specific to itself. > > > > Say BOX1 information was changed for some reason but > > we would still like > > to know the previous information for BOX1. > > > > CRATE1 is now related to BOX1,BOX1(old),BOX2 > >