Greetings,

I have some design questions regarding a multi-user application I am developing using Hibernate 3.2.2.ga.

When a user logs in, they are presented with their 'buddy list', a list of other users they have established a relationship with. In this table, among other columns, there is a column for displaying the date the logged-in user last sent that buddy a message, and another column displaying the date that the buddy last sent a message to the logged in user: 'last sent date' and 'last received date' essentially.

I am brainstorming the best way to design the schema and mappings to retrieve the buddy list with easily/quickly accessible message dates. In the full collection of messages for any given buddy, there will be many messages sent/received between a buddy and different users, which are irrelevant to the logged-in user, so I would like to limit the messages retrieved to be either to or from the logged-in user. Furthermore, I only need the last one sent and the last one received, so all others sent/received previously are also irrelevant to this data.

So far, all I can come up with is the following choices: have 2 collections of messages on my user object, one for sent and one for received, and when retrieving buddy lists 1. create a query that takes the logged-in user id and only retrieves buddies' messages that are to or from the logged-in user, with the largest date value 2. retrieve all buddies messages everytime and sort/iterate on the client side (very undesireable).
3. store the last sent/received dates only in a seperate join table between users and messages and update the fields everytime a message is sent. the table would act as a summary of all messages sent/received between two users.

Is there a better way I am overlooking?? Thanks. t-