For our membership administration application we are building a archive table. Members who are transcribed for 2 years have to be deleted from the actual tables and be moved to one big archive table. Because the procession of this membership application is relative big (2 million records in 10 year) we are building this because we want to prevent that the tables become to big. In this solution the current tables have an average of 2.5 million records and that will not grow with time. But this solution brings also much trouble because the data decencies. For example a person that could be archived have several related objects that cannot be deleted (such as payments, visitations, marriages, e.t.c.). The alternative is that we add a history flag on the person and exclude this category from overviews. This is a really simple solution but the person table will go growing with 2 million records in 10 year. Not really shocking in my onion (over 30 years maybe 10 million records) but I must have arguments for or against this solution in relation to performance. Would it affect query performance, assuming that we have an indexed attribute history and a simple addition to the where clause: history = false?
I still would not optimize this, unless you are certain that it is unmaintainable. The chance that the added complexity causes bugs and performance penalties are bigger than the chance you really improve anything by changing things there. Or, as the old IT saying goes: "premature optimization is the root of all evil". A well indexed server should be able to handle millions of records.
“We should forget about small
efficiencies, say about 97% of the
time: premature optimization is the
root of all evil. Yet we should not
pass up our opportunities in that
critical 3%. A good programmer will
not be lulled into complacency by such
reasoning, he will be wise to look
carefully at the critical code; but
only after that code has been
identified” - Donald Knuth
With a heavy server, enough memory, correct indexes, optimized retrieves in microflows (don't loop thru all persons) and optimized queries a database server can handle millions of records.
If this still is not the case you can consider splitting up the person entity into person and archiveperson and create the same associations for both.
Another option is to create an inherited entity (archiveperson) with the base entity (person) having all associations.