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: knappst...@beta-eigenheim.de Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
