Susan Harkins
harkins at iglou.com
Mon Jun 30 08:59:06 CDT 2003
You present an interesting situation. Supposing you were required to preserve the Autonumber -- how would you go about appending records as you archive -- you couldn't I'm guessing -- would you archive to new tables each time? What a mess. In addition, if you were trying to archive records from similar but different databases and you had conflicting AutoNumbers, what would you do? In the latter case, I'd say you could simply add a new AutoNumber to the historical table and work from that value instead of the original, but still, sounds like it could lead to a lot of confusion and maybe problems. I hadn't really considered either possibility. Susan H. > Martin, > > The reasons are varied, but in general, your trying to take a snapshot of > the way the data existed. This includes not only the entities themselves, > but the relationships as well. From a very purist point of view, that means > not modifying the data in any way. For example; capturing an image of a > hard disk for a criminal investigation. You must make a copy that is bit > for bit identical to the original. The process of copying cannot have the > potential to introduce errors or it will get tossed in court. Doesn't > matter if the process did or didn't introduce errors, just that the > possibility exists or not. IP tunneling is another example where the > original packet of data cannot be touched, so it gets encapsulated. It's a > very general concept that is applied in many situations. > > Then there are all the reasons why most relational systems today have > gravitated towards the use of surrogate keys; isolation from the data, > performance, decreased storage requirements, etc. These get to be critical > when talking about large data marts/warehouses in the Tera/Peta byte range. > > Isolation from the data is an important one as the data mart may span a > considerable time period. Some things that might happen: > > 1. Production DB keys might possibly be reused through normal operations. > 2. Production DB may re-use keys even though it is not supposed to. > 3. An entities attributes may change through time, but it's key may not. > 4. Key formats in a production system may change. > 5. Acquisition of another company may yield a situation where key sequences > conflict or keys are of a different type. > > > Some of these things are only valid if the existing system uses natural or > surrogate keys. Some apply to both. As I said the reasons are varied. > > > Jim Dettman > President, > Online Computer Services of WNY, Inc. > (315) 699-3443 > jimdettman at earthlink.net > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of > Mwp.Reid at Queens-Belfast.AC.UK > Sent: Sunday, June 29, 2003 3:40 PM > To: Access Developers discussion and problem solving > Subject: RE: [AccessD] AutoNumbers in historical tables > > > Just out of interest why would you want to? > > Martin > > Quoting Jim Dettman <jimdettman at earthlink.net>: > > > Susan, > > > > Depends on how much tractability you need/want. Typically in a data > > mart, > > you don't touch any of the original data including the keys, meaning > > that > > you add a new key structure outside of any existing. > > > > Also depends a lot on how your DB is structured: natural or > > surrogate. > > > > Jim Dettman > > President, > > Online Computer Services of WNY, Inc. > > (315) 699-3443 > > jimdettman at earthlink.net > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Susan > > Harkins > > Sent: Saturday, June 28, 2003 5:27 PM > > To: AccessD at databaseadvisors.com > > Subject: [AccessD] AutoNumbers in historical tables > > > > > > When archiving historical data do you change an AutoNumber field to a > > Number > > field? Like to hear pros and cons on the subject. > > > > Susan H. > > > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > >