I think that both approaches have pros and cons and so the decission
must be taken depending on the bussiness rules.

In favor of the "two tables" approach may be the case of a UNIQUE
constraint limited to current records: There can't be two current
records with the same value in a column, but the same value is allowed
if one is current and the other is historic or if both are historic.

In favor of the "all in one table" approach may be the case of a UNIQUE
constraint encompassing both kind of records: There can't be two records
with the same value in a column, regardless if their status (current or
historic).

Aldo Caruso


El 31/07/13 17:56, un_spoken escribió:

>
>
>
> --- In firebird-support@yahoogroups.com
> <mailto:firebird-support%40yahoogroups.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.
>
>

[Non-text portions of this message have been removed]