[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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
0
realcoding
Asked:
realcoding
  • 2
  • 2
1 Solution
 
8080_DiverCommented:
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

0
 
GhunaimaCommented:
0
 
realcodingAuthor Commented:
@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.
0
 
realcodingAuthor Commented:
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.
0
 
8080_DiverCommented:
SELECT 'Inbox' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isArchived = 0 union
SELECT 'Unread' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isRead = 0 union
SELECT 'Flagged' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isFlagged = 1 union
SELECT 'Drafts' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isDraft =1 union
SELECT 'Archived' as folder, COUNT(*) as Tally FROM Message...  WHERE [to]=[ufid] and isArchived = 1 union
SELECT 'Sent' as folder, COUNT(*) as Tally FROM Message...  WHERE [from]=[ufid] 

Open in new window


The above is simply a restatement of your original question except in psuedo-SQL.  The part that you don't know how to do (e.g. WHERE [to]=[ufid] and isArchived = 0) is the part I am trying to address.  If you don't include the system folders in the Folders table, how do you plan to get the data associated with the decision rules?  Not including the system folders in your Folder table means that you are going to have to, in effect, plug some information and that means that you a) are not actually pulling the Tally data from your database and b) are introducing potention maintenance issues.

If you substitute a LEFT OUTER JOIN into the query I provided, it should get you close to the right results.  

However, if you will go ahead and include the system folders in your Folders table, you don't need to bother with the the LEFT OUTER JOIN if you start from the Folders table.  By doing so, you will ensure that all Folders are included in the output, whether they have messages or not.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now