Re: DB Design question
Date: Fri, 16 May 2003 14:06:01 +0300
Message-ID: <3EC4C619.2040107_at_atbusiness.com>
> 2. Every minute his program measures how busy a system is and then he
> only wants to store the _last_ 50 of these measurements.
You could try something like this:
create table measurement( mes_id, mes_mod, mes_1, mes_2 etc...,
unique(mes_id), unique(mes_mod) )
insert into measurement values(1,1,0,...) -- seed row
Then for each measurement you want to save do this :
select max(mes_id)+1 into :max_mes_id from measurement;
mes_mod = MOD(max_mes_id,50);
select count(*) into :count from measurement where mes_mod = :mes_mod;
delete from measurement where mes_mod = :mes_mod;
insert into measurement values( :max_mes_id, mes_mod, .... );
Because MOD(x,50) returns numbers 0...49 in a round robin fashion you
will be guaranteed to have
the 50 latest measurements in the table.
I hope this is what you are looking for...
regards,
Lauri Pietarinen
Received on Fri May 16 2003 - 13:06:01 CEST