--- In firebird-support@yahoogroups.com, Aldo Caruso <aldo.caruso@...> wrote:

>
> Lester,
>
> Thanks for your advice. I'll take it into account, because also I
> think that having two tables is a duplication of work ( not only for
> moving records between them but also for maintaining DDL changes
> synchronized )
>
> Aldo Caruso
>

I've been working with both approaches (history in the same table as regular data and in a separate table). Currently in my company we are in process of transition from "history in one table" approach. It gets ugly.. especially if you need to have an UNIQUE index on some columns. It's also requires from you to remember that in almost each query you want to show only the current data.

DDL changes which you are afraid of are not that scary if you have written automated tests. Our tests are checking if everytings is copied correctly to the history data. So for example if we add a column in the main table or modify its size and forgot to mirror those changes in history table, our tests will tell us that right away. Copying is done by the trigger and if you think good on your design you can write one test which will be able to check all your tables and history triggers.

Regards.