Help with complex database design required (lengthy post)
Complex for me anyways. Hopefully this explains well enough what I need.
I am creating a web site that displays "Advertisements" in a variety of formats and in various "Locations" on each page. Each Advertisement has a specific "Level", which indicates the "Location(s)" where the ad can be shown. Advertisements are also placed into Categories and SubCategories, which, also depending on their Level, can be multiple Categories/SubCategories. The site also contains a "Directory page" based on these Categories/SubCategories. Advertisements contain a Member flag. Member ads are shown before Non-Member ads within the same Level.
Here are the current business rules for each page except for the Directory Page which has its own rules. On every web page there are 3 Locations where Advertisements are listed. Where applicable, Advertisements must be given the chance to be at the top of the list.
The first Location is strictly for Level 1 advertisements. This Location can show 5 advertisements at once. As the site is navigated advertisements start in position 5 and then move up through to position 1.
The second Location is for Levels 1 and 2. Here, only one ad is shown at a time, however each advertisement must be shown at least once before returning to the start of the order.
The third Location is for Levels 1, 2 and 3. Similar to the first Location, in that their are 5 advertisements shown at a time and they move up through to position 1.
The rules for the Directory page, which is a Treeview of the Category and SubCategory items, are as follows
If the User clicks a Category, Level 1 and 2 advertisements are displayed in a grid. The sorting is by the Member Flag, however each advertisement must get a chance to be at the top of the list. Each Non-Member advertisement must be given a chance to be the first one displayed after the Member ads.
If the User clicks a SubCategory, all Levels are displayed in the Grid. The sorting in this case is by
Level, except that Levels 1 and 2 should be treated the same, then by the Member flag. Again, each advertisement must be given a chance to get to the top of the list within their respective Level.
Note - The order advertisements appear in a list are on a Global basis, not per session. For example, if one user is navigating through the "Directory Page" and they click a Category this would be displayed.
Ad 1 - Members
Ad 2
Ad 3
Ad 4 - Non Members
Ad 5
If they click that same Category then the order would be
Ad 2 - Members
Ad 3
Ad 1
Ad 5 - Non Members
Ad 4
Now if another user enters the site and clicks that Category as well - the order would be
Ad 3 - Members
Ad 1
Ad 2
Ad 4 - Non Members
Ad 5
if the original user one again clicks that Category they will see the following order. To this user it would look like the #1 ad jumped from the bottom to the top and the non-member ads stayed the same. Which is okay, thats what we want.
Ad 1 - Members
Ad 2
Ad 3
Ad 5 - Non Members
Ad 4
I have tried a couple of designs but with each one, I find I need to call way too many update sql statements to keep control of these "View Orders". These update statements tend to include a Top 1 clause and a Select statement where I am hard coding the Level and Member flag. I need to limit the amount of updates and sql statements that must be executed.
One more thing, I also need to keep Statistics on the number of times an advertisement was displayed(among other things) per month, per Location.
Pseudo code when a user clicks a SubCategory
Select All Advertisements in the Category/SubCategory
Sort by Ad Level, Member Flag, View Order
Update the View Order for the Top most (move ad at top to bottom of list) ads for each Level, Member flag.
Update or Insert Statistics for this Advertisement. Need to include the Advertisement Unique Id, a Stat type (in this case SubCategory), Year, Month)
Any guidance or assistance would be appreciated. Thanks.