As requested, I have created a new thread with this topic,
If ~100 years of data exists, and thus needs to be manage but only ~1 year (current projects) of information is accessed on a consistent basis that represents only 1% of the data.
Why not create two databases a day to day operating one, and then an archive of closed out info? Closed out jobs might get accessed on occasion, very rarely, or if ever at all. If fact why not compile the data that goes into the archive as a large text document (ie inspector notes) and still save off the large pdfs. This would dramatically reduce the size of the database and complexity of the archive data and keep its format very simple. That said the offset will be the writing and keeping up with the compiler as the database morphs. I would think backups would be much simpler with the core of the archive data never changing.
Now with all the archive data stripped from the operating database and it only containing ~1 year of active data it would seem that no extreme measures should be mandated to guard constant growth.
What are your thoughts,