Help with complex sql query

Question

To make story short, i'm building self-learning banner management system. Users will be able to insert these banners to their site when banners will be shown based on sales/impressions ratio.

I have 4 tables

Banners 
bannerID      int
bannerImage   varchar....

SmartBanners
smartBannerID      int
smartBannerArrayID int
bannerID           int
impressionsCount   int
visibility         tinyint (percents)

SmartBannerArrays 
smartBannerArrayID int
userID             int

Statistics
bannerID          int
saleAmountPerDay  decimal...

Each night i need to generate new "visibility" for each SmartBanner based on whole SmartBannerArray that same user has. So i need to get sum of impressions and sales for each bannerID in SmartBannerArray.

All comes to my mind is to use double cursor, one will loop thought SmartBannerArrays get needed values for sum of impressions and sales and then inner loop which will access each SmartBanner and change it's "visibility" percentage based on (sales/impressions)/(sumOfSales/sumOfImpressions)*100

Hope you get the picture...

Is there any other way to design better tables or not to use double cursor to avoid server overload ?

MORE INFO Each sale written to Statistics table , field is updated because i need daily sum per banner rather each sale. User create BannerArray, choose products he would like to promote. Each product he chose is written to Banners table with proper image and other info. SmartBanners table stores bannerID so as Statistics table, while BannerArray table assigns this group of banners to certain user.

Answer 1

The picture is not so clear. Why cursors at all, what is wrong with this?

SELECT SUM(saleAmountPerDay)/SUM(impressionsCount) 
FROM SmartBanners sb        INNER JOIN
     SmartBannerArrays sba  ON sb.smartBannerArrayID = sba.smartBannerArrayID INNER JOIN
     Statistics ss          ON sb.bannerID = ss.bannerID 
GROUP BY smartBannerArrayID

Which can be used then as subquery to calculate 'visibility' directly.

EDIT2: Illustrating the principle (SQL not optimized), why not:

UPDATE SmartBanners
SET visibility = 
    ROUND( 100. * 
    (
    SELECT SUM(saleAmountPerDay)/SUM(impressionsCount) 
    FROM SmartBanners sb        INNER JOIN
         Statistics ss          ON sb.bannerID = ss.bannerID 
    WHERE sb.smartBannerID = SmartBAnners.smartBannerID
    ) /
    (
    SELECT SUM(saleAmountPerDay)/SUM(impressionsCount) 
    FROM SmartBanners sb        INNER JOIN
         SmartBannerArrays sba  ON sb.smartBannerArrayID = sba.smartBannerArrayID INNER JOIN
         Statistics ss          ON sb.bannerID = ss.bannerID 
    WHERE sb.smartBannerArrayID = SmartBAnners.smartBannerArrayID
    ) )

if you want to avoid the cursors.

Also, not to forget - if this data is not going to be updated often (what's the frequency of the update of the statistics table? I imagine you update it periodically from some logs) AND if you are looking to maximize read performance you might consider triggers.

EDIT3: In MS SQL you can also use OVER clause, just a short example

SELECT 
       SUM(saleAmountPerDay) OVER(PARTITION BY BannerID) AS 'TotalSalesByBanner',
       SUM(impressionsCount) OVER(PARTITION BY BannerID) AS 'TotalImpressionsByBanner',
       SUM(saleAmountPerDay) OVER(PARTITION BY smartBannerArrayID) AS 'TotalSalesByArray',
       SUM(impressionsCount) OVER(PARTITION BY smartBannerArrayID) AS 'TotalImpressionsByBanner'    
FROM   
       SmartBanners sb INNER JOIN
       Statistics ss   ON sb.bannerID = ss.bannerID
GROUP BY
       SmartBannerID

This SQL is not tested.

Answer 2

In many cases the use of cursors can be avoided by using derived tables. An introduction to the use of them can be found here.

Answer 3

I think you're asking the right question, which is: is there any way to design better tables? My advice: worry less about server overload, which is no big deal if you're running queries only once a day. Worry more about a comprehensible structure.

What about this:

 Banners
 -------
 BannerID
 UserID
 BannerImage
 BannerVisibility  // A denormalized value that you calculate once a day

 Banner_Sales
 ------------
 BannerID
 Date
 SalesAmt

 Banner_Impressions
 ------------------
 BannerID
 Date
 Impressions

You don't need a SmartBannerArray - or cursors - to sum the sales and impressions. Just use a SQL sum:

 SELECT SUM(bs.SalesAmt) AS SALES, SUM(bi.Impressions) AS IMPRESSIONS, 
   SUM(bs.SalesAmt) / SUM(bi.Impressions) AS RATIO
 FROM Banners b
 JOIN Banner_Sales bs
 JOIN Banner_Impressions bi
 WHERE b.UserID = ? // For total sales / impressions for a user

Substitute

WHERE b.BannerID = ? // For total sales / impressions for a banner

Good luck!

P.S. tinyint's are for integers, not percentages. You'll need a double. :)