[Gnumed-devel] Database (table) size management question

[Jim Busser]

When I wrote an application years ago in FoxBASE to handle patient 
billings, the table of course grew and grew and so I elected at 
intervals to "transfer" records from the "active billing" table to a 
"paid or written off" table (i.e. by appending records, and then 
deleting the originals from the "active billing" table).

My reasons for doing so at the time were in the hope that each new 
billing record could more quickly be written, also to speed indexing 
and lookups from said table, and to keep down the total number of 
records that appeared to need work. There were of course some downsides 
- FoxBASE did not support joins, and there was no way to view a 
superset of records across both tables, unless one were to export them 
to a third, temporary table and then delete the redundant copies. They 
therefore had to be viewed in two separate steps.

Onto PosgreSQL in GnuMed, is there a reason to do periodic transfers of 
records from one table to another, or is there no advantage to doing 
so, only drawbacks? Also it would be helpful to know what form any 
"archiving" or "purging" is preferred to take.

In Canada, the tax people require that we keep all financial records 
for at least the current year plus six. Obviously clinical data is 
desirable to keep longer even when it is not required by law, and even 
the laws may change as after Canada's "tainted blood" (Hepatitis C) 
scandal, a moratorium was issued against our hospitals being able to 
destroy ANY long-term records, no matter their age.