Do you guys offer Data Archiving within same DB?

BenHayat

Suppose you have an application that creates invoices and invoice items. Once the customer receives goods invoice is paid, what do you do with the Invoice and it's children?

A) Do you flag it as "Paid/Closed" a leave it in the same table? - If yes, doesn't the table (and it's children tables) get bloated and slow down the system for queries and creating new records?

B) Do you flag them to be archived and then through some process, you simply create NEW object in another table (AR_Invoice) and copy the invoice data (and it's children) to related Archived tables and then delete the original records, to keep the original Invoice table small for "Open/Active" invoices?

C) Other ideas are welcomed.

Thanks in advance!
..Ben

Jaymer

A) "bloated and slow down" - well, I't certainly depends on how many recs you're considering, but I don't think a big server with MSSQL is going to be bothered with a couple of 100,000 "old" invoices.

B) If I archive them, then I have to look TWO places when researching old invoices.

C) Unless you use Business Object Groups, and All_Invoices is a union of both Current_Invoice and Archived_Invoice BOs... but then that add overhead when using the Group. I wonder which uses more Overhead... a UNION of Queries on the group, or extra Insert/index updating time if using just 1 table.

BenHayat

Jaymer wrote
A) "bloated and slow down" - well, I't certainly depends on how many recs you're considering, but I don't think a big server with MSSQL is going to be bothered with a couple of 100,000 "old" invoices.

I wasn't so concerned about the DB engine, but more so OLD invoices (closed) affecting Aware during day-to-day "Open" invoices.
[Invoice] was just a made up case for illustration but serves a good purpose for "Open" and Closed" object. I want the have the smallest # of "Open" records in the invoice table, so all daily queries are fast. That is the main reason for archive.

Jaymer wrote
B) If I archive them, then I have to look TWO places when researching old invoices.

The "Archived" records are mainly going to be for "Analytical" process and sometimes for "View". But they don't get added to the "Open/active" records, like Court cases.

So, having said that, do you still think it's worth it to keep them into two data groups? It's not only the Invoice header. Each Invoice has 10 different child tables. Like a table for all notes. A table for all images of that invoice. A table of all parts involved in that invoice and etc.
I should ONLY go through this IF Aware suffers from large # of data (that I have not had experienced with Aware large dataset).