Help with complex sql query
- by eugeneK
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 ?
