i have table containing balances of 100 accounts (it's variable). 1 record per account. balances continually updated find best way archive current balance each day.
i'm looking efficient way this.
table schemas:
-- -------------------------------------------------------- -- -- table structure table `acc_bals` -- -- holds tracks balances of coa's , bank accounts create table if not exists `acc_bals` ( `id` int(11) not null auto_increment, `acc_type` tinyint(4) not null comment '1 - coa; 2 - bank accounts', `acc_id` smallint(5) not null, `acct_balance` varbinary(40) not null, primary key (id) ) engine=innodb default charset=utf8 auto_increment=1; -- -- table structure table `balance_archive` -- create table if not exists `balance_archive` ( `id` mediumint(6) unsigned not null auto_increment, `date` date not null comment 'beginning of day value archived for..', `coa_id` smallint(4) unsigned not null comment 'foreign id of coa.', `bal` varbinary(27) not null comment 'archived coa balance @ beginning of specified date.', primary key (`id`) ) engine=innodb default charset=utf8 auto_increment=1 ; the reason varbinary columns because balances encrypted.
i thinking query acc_bals , put account id's , values array, having decrypted values, , run second query , each item in array, copy archive table.
it occurred me not need decrypt values @ save lot of processing , further more, might possible in single query?
if approach seems right, perhaps can suggest how query might please?
i'm using mysql pdo.
simple select in recurring event set each day. :
insert balance_archive (date,coa_id,bal) select now(),ab.id,ab.acct_balance acc_bals ab that way don't need use php @ all, can mysql only.
Comments
Post a Comment