Aldo,
I don't know if my approach is a good approach, but it is good enough
for me: I keep a separate database for that purpose, to make use of
the different access patterns.

Like you say, in comparison with the "current" main database, there
is much less access to these tables. And what is left usually is a
read-only access as well. Records never get deleted, never get
updated, only inserted, and I can't remember even one transaction that
needed to be rolled back.

I have more information for each record, though (WHEN was it replaced
by a new version, WHO replaced it, and WHY has it been replaced)

I have different strategies for moving the records from CURRENT to
HISTORY. If the overall process does not take too long, I move them at
once. If history records must be created because of batch updates, I
only tag them, and the moving is done as part of a scheduled
housekeeping.

> Hi,

> it is common in some business rules that a table has a companion
> table of historic records. The historic table has the same structure
> than the active records table, but is less accessed than the active
> table. On the other hand, the historic table grows steadly, ending up
> with far more records than the active one ( records deleted from the
> active table go to the historic one ).

> My question is the following: should both tables be merged into a
> single table, with an additional field marking historic records ? Will
> the queries on this table be slower than having them separated ?

> I know that an index taking into account that additional field
> would speed up the queries but, on the other hand, such an index would
> be a very poorly selective one, because the additional field would only
> have two values ( 0 and 1 ) like any boolean field.

> Thanks for any advice.
> Aldo



> ------------------------------------

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !

> Also search the knowledgebases at http://www.ibphoenix.com

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links

mit freundlichen Grüßen,

André Knappstein
EDV und Controlling
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH
Hafenweg 4
59192 Bergkamen-Rünthe

Telefon: +49 2389 9240 140
Telefax: +49 2389 9240 150
e-mail: knappstein@...

Amtsgericht Hamm Nr. B 420
Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus

USt-IDNr.: DE 125215402