Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries

Quote Reply
Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries In reply to
I've learned a bit about database technology since I wrote that post, so I think I can give you some useful but incomplete information.

The short answer is that no database system can constantly update data, since that would require an infinite number of queries (always checking to see if it needs to be changed and changing it if it does). Not only is that technically impossible, but even an attempt to come close to "constant" updates/calculations would be extremely inefficient.

I don't have any real experience with commercial db systems (Oracle, Sybase, etc.), but my understanding is that they accomplish this with "triggers". Basically, everytime a given action is performed (a record is viewed, for example) another corresponding action is also performed (the update calculation). As far as I can tell, that's how the big boys solve this problem.

MySQL does not have trigger technology, however, so you've got to find a way to work around that. I've used cron jobs with quite a bit of success. Since I only really need my "duration" data to be calculated daily, I wrote a very simple update script that runs by cron at midnight every day and updates all the records. Maybe that's not the most efficient way to accomplish this, but it works perfectly for my limited needs.

Since your bank system will presumably need far more frequent updating, you might need to take a more sophisticated approach. I do have some experience programming basic online accounting systems, which is probably closer to what you're talking about. In that case, I handled the update/calculation issue by building them into the relevant functions/subroutines - sort of a pseudo-trigger. So if you have a subroutine for cash withdrawals, say, you just build into it two queries: one that reduces the balance in the account and one that increases the balance in the wallet.

That approach only works if you're writing a script from scratch, of course. If you're using DBManSQL (that's what this forum is for, after all) then you'd probably need to write a plugin(s) to accomplish the same thing.

Hope that helps.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Subject Author Views Date
Thread Pre-Sales Question - Can the tables calculate entries Ground Zero 4935 Mar 28, 2002, 12:48 AM
Thread Re: [Ground Zero] Pre-Sales Question - Can the tables calculate entries
jean 4858 Apr 3, 2002, 11:20 AM
Thread Re: [jean] Pre-Sales Question - Can the tables calculate entries
hennagaijin 4719 Jun 6, 2002, 6:28 AM
Thread Re: [hennagaijin] Pre-Sales Question - Can the tables calculate entries
ArtistikDD 4450 Dec 2, 2002, 8:39 PM
Thread Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries
hennagaijin 4476 Dec 3, 2002, 6:30 AM
Thread Re: [hennagaijin] Pre-Sales Question - Can the tables calculate entries
ArtistikDD 4426 Dec 3, 2002, 7:43 PM
Post Re: [ArtistikDD] Pre-Sales Question - Can the tables calculate entries
NetAgent 4296 Jan 29, 2003, 10:19 AM