Hi,

> I need to implement the following functionality.
>
> There is an appserver downloading mail and storing it in a database. It

should keep track of how many mails have been downloaded.

> When the client connects to the database I want to show one of those

popups saying "you have :N new mails". After that if the user logs in, it
should not show this popup. If the client is running and a new mail is
downloaded it should again pop up a message

>

You might want to try EVENTs here.

> Tried creating a record and updating it each time a mail was downloaded.

The client used to check on startup and a timer tick. If there was a number
it would popup and reset it to 0. This lead to some pesky random update
conflicts which was traced down to this.

>
> So lurking on this list I read some mails which was talking about keeping

track of stock etc. And so I switched to inserting a record for each
downloaded mail and then do a count - display and delete those records so
the update conflict problem went away. But count is becoming very slow.
(Many Many users mail are stored in the same database so the count has a
where condition on userid)

>
> Also we need to find out number of mails in a particular folder etc. Count

is dead slow.

>
> So What is the best way to do something like this in Firebird.
>
>
> The data structure is like this.
> I have table tblMessages which has all message. A tblMessageFolderRelation

which has MessageID and FolderID.

> I want to display number of message in each folder. Best way to do this.

You can easily show the (new) count if you add a table

NEW_MAIL
MessageID
FolderID

Remove the record from the NEW_MAIL table after viewing and your
COUNT(...new mail...)
will always be fast as it's a low number.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com