Re: [firebird-support] Re: Historic tables design
Alexandre Benson Smith wrote: Em 31/7/2013 21:38, Iwan Cahyadi Sugeng escreveu: I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update I don't know your logic and perhaps this comment doesn't apply to your case, but anyway... I think it's better to check the metadata and stop/report about some misleading table information, it's not recommended to update metadata with the database in use. I'd second that ... Managing metadata should ALWAYS be detached from anything to do with data. In fact the only metadata changes to our historic data over the years has been the addition of a few extra fields. And Aldo's comment about 'UNIQUE' constraint also applies. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
Re: [firebird-support] Re: Historic tables design
On 1 Aug 2013 13:47, Lester Caine les...@lsces.co.uk wrote: I'd second that ... Managing metadata should ALWAYS be detached from anything to do with data. In fact the only metadata changes to our historic data over the years has been the addition of a few extra fields. And Aldo's comment about 'UNIQUE' constraint also applies. -- Lester Caine - G8HFL - I agree that we should not having metadata changes at runtime, but in my design i plan to automatically generate a historical table based on setting, so the server application is responsible to synchronize the table definition. This is only planned, i haven't implement it yet. I choose this in order to simplify the application development and make it more robust [Non-text portions of this message have been removed]
[firebird-support] Re: Historic tables design
--- 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.
Re: [firebird-support] Re: Historic tables design
On 1 Aug 2013 04:12, un_spoken brucedickin...@wp.pl wrote: --- 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. I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Historic tables design
Em 31/7/2013 21:38, Iwan Cahyadi Sugeng escreveu: I plan to check the historical table on system start and update the metadata. I'm using n-tier solution, so my server application will do the metadata update I don't know your logic and perhaps this comment doesn't apply to your case, but anyway... I think it's better to check the metadata and stop/report about some misleading table information, it's not recommended to update metadata with the database in use. see you !
Re: [firebird-support] Re: Historic tables design
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]
