Re: DB Design question

> 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 - 06:06:01 CDT