Jim Dettman
jimdettman at earthlink.net
Mon Jun 30 08:20:50 CDT 2003
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