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]
