Advice for Archiving, Set Up Table Relationships First?

[AlexB123]

Hi all,

I have an existing database that needs to have a large number of records archived from various tables. In the future they will be archived according to a set of criteria, such as the age of the item and its status.

My understanding is that I need to be very careful of respecting referential integrity when I append records to an archive db and then delete the records in the current one. To that end, I was investigating table relationships, and it does not seem that any have been set up in the current db. Do I need these relationships to design my archive db and functions?

Is it best to go back and set up relationships now? If I did that, can that compromise the db? How can I research and find which tables need the relationships? If and when I can set up the relationships, will referential integrity rules within Access help me to ensure the stability and reliability of the current db once I start archiving and moving records?

Any resources or suggestions?

Thanks!

 

[xenou]

I suppose it all depends. Do you think the data you want to archive has a referential relationship with other tables? Then take it from there. Generally setting up your relationships explicitly is considered a good thing. That said, it is possible for database to work even if you don't.

 

[AlexB123]

I suppose it comes down to how much time I have to invest then?

If I were to proceed carefully, without setting up the relationships, how can I test afterwards to see if referential integrity or reliability is preserved? Will the DB provide explicit errors?

 

[xenou]

I don't think you will find you get any specific errors. If you are talking about your archived data, I still think it would be easier to speak to that directly with some knowledge of what exactly are the relationships you want to preserve. But in general, if, for instance, you are archiving orders, you might check that every order head that you archive has a related set of order detail records. If you archive your data properly by archiving the order details that go with the order heads, then you really shouldn't have any problems. If you didn't do that then you'd just be left with order details in one place (the original database) that have no order heads, and order heads in another place (the archive) that have no order details. But of course if you wanted to do you could fix it still - just put the order heads back, or archive the order details too.

 

[Joe4]

FWIW, I have set up many databases that have been used for many years without ever setting up relationships. So it certainly isn't necessary.

From what I have gathered over the year, it appears that if they are set-up and data clean-up is handled correctly, they could be beneficial.
However, they could impose a burden if not data clrsn-up is not handled correctly (specifically, if cleaning up data not handled in a way that works within the structure of the relationships).