>Are you suggesting to keep all records (estimates and archived) together only they would have restrictions based on their status code and depending on their permissions users are allowed accesss to process certain records. This may have some merrit.

The "status codes" are only suggestions; use whichever ones are appropriate to your needs. And yes, user access to sets of records is based on record codes and user access to those codes (this implies a "user table" with access rules).

Initially, all records (regardless of status) could be kept in one set of tables; as the volume grows, you might consider another set (same format, different directory) into which you archive.

>There is still the issue of the invoice total being based on calculations from the link between order records and code file which if archived records
>maintained would change the invoice total when prices change in the future.

Even though it is "derived" information, I have always stored "pricing" information (which was pulled from various sources) in the order tables. At "order valuation" time, the user had the option of using "current pricing" (based on current prices, costs, etc.) or "prior pricing" (ie. using the "prices" that were previously stored in the order).

Besides pricing that was based on tables and current average costs, the user also had the option to input a specific "fixed" price for an order item that would superceed any automatic default pricing.

If an "archived" order had to be rebilled, it was restored into the production tables and "reopened" for billing purposes (usually using the "prior pricing" option to preserve the old prices for items that did not change).

In reality, one usually waits as long as possible to archive orders (at least 3 months). It's a given fact that as soon as an order is archived, it needs to be revised/rebilled.