« - »

Separate Database For Each Transaction Period ??

Posted on 3 March 2005

Yesterday, I had a phone call from an old friend of mine. He asked me whether we can have SELECT for different databases in Firebird or not. Being curious, I asked him why he needed such a thing. He explained that he stored transactions for each period in separate database. Wow !! I think that concept is applicable and suitable for desktop applications only, which cannot handle large number of records in the tables. In Xbase world, we usually separate the transactions into different directories (oops, well we call it databases in RDBMS *grin*) to optimize the database access speed and also to minimize the chance of data corruption.

However, with such a good RDBMS like Firebird, do we still need to apply that concept ?? I personally feel it is awkward. If we store monthly transactions into separate database, I cannot imagine when we want to have a yearly comparison reporting; it means we will make 12 concurrent connections per user!! Yikes, the server will choke extensively. Also when we have to enter a due transaction which should update the closing balance for every month consecutively (i.e. the current period is March 2005 and we need to input corrective transaction in December 2004, that means we need to connect to database December 2004, January 2005, February 2005, and finally March 2005 to update all closing balance ?? What a mess).

My personal opinion is to have certain number of periods in the active database (i.e. 12 months) and anything beyond that will be stored in an archive database automatically without user interventions. If the users need to access the data in the archive database, we can provide a read-only access to it. In this way, the database connections are kept to minimum and the headache of maintaining a lot of database is eliminated.

It is just my 0.02$, let me know what you think.

Popularity: 2% [?]

Komentar dan masukan tentang artikel ini akan sangat bermanfaat bagi semua orang. Silakan isi form komentar di bawah ini. Terimakasih sebelumnya!

Leave a reply