Re: DB Design question

Hi Naran,

yes, I changed strategy and I forgot to remove that line. You could of course update/insert depending on whether there was an existing row previously.

Nice to hear that it worked.

regards,
Lauri Pietarinen

Naran Hirani wrote:

> 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
>>
>>
>>
>>
>

-- 
________________________________________________________________

 Lauri Pietarinen, Senior Consultant, Databases

 AtBusiness Communications Oyj, Kaapeliaukio 1, FIN-00180 Helsinki

 tel. +358-9-2311 6632,  mob. +358-50-594 2011,  fax +358-9-2311 6601
 http://www.atbusiness.com,  email: lauri.pietarinen_at_atbusiness.com
_____________________________________________________________________