Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was just wondering, what would be the table structure for a messaging system similar to Gmail's priority inbox? If you're unfamiliar with the system, it prioritises messages based on the type of messages you most often read and those to which you reply, then separates them from 'normal' messages. In this hypothetical scenario, I'm basing the 'score' only on senders to which the user replies.

I was thinking that each user would have a table assigned to them that recorded the user ids of the top 10 people they responded to, each user name requiring 5 replies to be entered into said table. For each reply, the 'replied' value would increment by one. Is that the best/most logical way of doing it?

I'm not familiar with 'NoSQL' databases, so my logic is based on a relational methodology. My apologies if I'm missing something obvious because of that.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

I think the trick to this is that it doesn't have to be real time, just eventually consistent, in which case it's straightforward enough (using SQL Server, but this applies in any DB). First a trivial table and some sample data:

create table messages 
(message_id integer, sender varchar(20), recipient varchar (20))
go

insert into messages values (1, 'Gaius', 'Octavian')
insert into messages values (2, 'Gaius', 'Octavian')
insert into messages values (3, 'Gaius', 'Octavian')
insert into messages values (4, 'Aurelius', 'Octavian')
insert into messages values (5, 'Aurelius', 'Octavian')
insert into messages values (6, 'Aurelius', 'Gaius')
insert into messages values (7, 'Aurelius', 'Gaius')
insert into messages values (8, 'Octavian', 'Gaius')
go

This is logging for every message, who sent it and who to (assuming for simplicity that the message body is stored in another table). So we can see that the top sender to Octavian is Gaius (3 messages of 5), and the top sender to Gaius is Aurelius (2 messages of 3). To query that using a CTE:

with q1 as (
  select recipient, sender, count(sender) as num_messages_from_sender,
  rank() over (partition by recipient order by count(sender) desc) as priority
  from messages group by recipient, sender)
select recipient, sender as top_sender, num_messages_from_sender 
from q1 where priority=1
go

In practice you would have a job that ran every minute (or whatever interval is best) refreshing a lookup table mapping a user to their top sender (or top n senders using where priority <= n) (or in your case, you would be tracking the senders to which they reply with another column and filtering by that).

For the sake of simplicity I have left off indexes and partitioning - they would be the key to performance of this solution. You could certainly scale this to many billions of messages on any modern DB/hardware. GMail most likely has a custom solution tho', but with 20,000 engineers Google can do that!

share|improve this answer
add comment

Here is a thought. Track all replies by all users in one table that has a unique entry for each user key and reply address. Store a count of replies and perhaps a date of the last entry. A query could be done for any user retrieving the top ten most responded to addresses. Infrequently used entries could be periodically purged based on the number of replies and when the last reply was.

share|improve this answer
    
Thanks, but would that not slow things down a lot? Everything being in one table, I mean. I'm a novice when it comes to database design, as you can probably tell. –  britishcoder Apr 6 '11 at 21:33
    
If you are not google, that should not be the problem. If you are google-like, you should go away from sql and relational databases and use something like bigtable. –  rvs Apr 7 '11 at 6:28
1  
rsv is right. In addition, although I doubt speed would be a problem, if it were you could take advantage of partitioning by user or a materialized views with a row for each user and a comma separated list of the top ten addresses. A separate table for each user would create more problems than it would solve. –  Leigh Riffel Apr 7 '11 at 13:05
add comment

The back end of the system will have an address book table for the contacts pertaining to the user. Add two columns (read,replied) to the address book table of the email account of that user. Every time the user reads or replies to a give contact in the address book, bump up a counter. Use a formula like read+replied count, to prioritize email for that contact.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.