Evil Kraig F (10/22/2012)
Eventually everything in a shipment/order would have to age past the 2 year mark, at which point I'd archive. Even though yes, they chain, at some point everything in some order and some shipment would have to get past 2 years.
Yes but they would always be linked to something less than 2 years old ...
Evil Kraig F (10/22/2012)
If I needed another alternative, what I'd most likely end up doing is talking with business and finding out when they're unlikely to ever need shipment information again. Once an order archived, I'd copy the shipment header to the archive if it wasn't there and then move that order's pieces of the shipment over, removing them from the primary DB. Eventually a shipment would be empty in the primary and I'd remove the header, finally fully archiving it.
Yes, that is what I will do
Unfortunately this impacts on the business rules so a careful redesign is required to ensure the application never sees a partially archived Order ...
Moral of the story: always design and test your archiving procedures before application goes live ... not all db designs allow for simple "follow the references" archiving