For context, i am including a DB schema image.
I have an email type application and want to select all the messages (inbox, sent, archived...) for a user. The problem is that i normilize the header part of the emails into the DB such that the flat data goes into a Message table and the from,to,cc,bcc get stored to another table.
whats the best way to select # of messages in each of my desired aggregations such as inbox, sent, archived, read, flagged as per the PK/ FK relationship betweek the message table and the recipient table?
one thing i am placing much weight on is the efficiency of the sql solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB
here is how i can get each of my required aggregations:
SELECT * FROM Message...
Inbox WHERE [to] = [ufid] and isArchived = 0
Unread WHERE [to] = [ufid] and isRead = 0
Flagged WHERE [to] = [ufid] and isFlagged = 1
Drafts WHERE [to] = [ufid] and isDraft = 1
Sent WHERE [from] = [ufid]
Archived WHERE [to] = [ufid] and isArchived = 1
my desired result would be :
FOLDER | TALLY
inbox | 20
unread | 10
flagged | 2
draft | 5
sent | 11
archived | 12