Re: DB Design question

Hi Lauri,

I think, if I am not mistaken, the line:

    select count(*) into :count from measurement where mes_mod = :mes_mod;

in your suggestion is surplus to requirement but other than that it all works very nicely
and fits the bill exactly.

Cheers.
Naran.

Lauri Pietarinen wrote:

>> 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 - 17:35:55 CEST