asked on

how to effectivley query a normilized SQL db w/ aggregats?

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

DB-Schema-V3.GIF

8080_Diver

See how the following works for you.

SELECT Folder
  ,SUM(
        CASE
            WHEN Type = 'To'
                AND isArchived = 0
                AND Folder = 'Inbox'
            THEN 1
            WHEN Type = 'To'
                AND isRead = 0
                AND Folder = 'Unread'
            THEN 1
            WHEN Type = 'To'
                AND isFlagged = 1
                AND Folder = 'Flagged'
            THEN 1
            WHEN Type = 'To'
                AND isDraft = 1
                AND Folder = 'Drafts'
            THEN 1
            WHEN Type = 'From'
                AND Folder = 'Sent'
            THEN 1
            WHEN Type = 'To'
                AND isArchived = 1
                AND Folder = 'Archived'
            THEN 1
            ELSE 0
        END) Tally
FROM
    (SELECT F.Folder
      ,RT.Type
      ,M.isArchived
      ,M.isRead
      ,M.isFlagged
      ,M.isDraft
    FROM MESSAGE M
    INNER JOIN MessageSource MS
    ON  MS.ID = M.MessageSourceID
    INNER JOIN MessageRecipient MR
    ON  MR.MessageID = M.ID
    INNER JOIN RecipientType R
    ON  R.ID = MR.MessageRecipientID
    INNER JOIN Folder F
    ON  M.FolderID = F.ID
    INNER JOIN USER U
    ON  U.ID = F.UserID
    WHERE RT.Type IN('To'
                   ,'From')
    ) T;

Open in new window

G Trurab Khan

sql1.sql

realcoding

ASKER

@8080_Diver: the folders table is for user defined folders only. system folders such as inbox,, sent are defined in the SQL logic in the question, I can derive them based on the logic posted above. this eliminates the need for having folder records for these system folders.

realcoding

ASKER

8080_Diver: the sql would definitely need a left outer join to folder table to always include folders even if there are no messages in the folder.

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial