Help with complex database design required (lengthy post)

[brucevde]

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.

[Ecniv]

Hi,

I suspect you've already got a design somewhat like this:
Advertisements
AdID - autonumber - PK
PicturePath - text 255
PictureText - text 255
Level - byte
CatID - Long
SubCatID - Long
IsMember - Y/N (default No)

(Two choices?)
Statistics
StatsID - Autonumber - PK?
AdID - Long
LocationNo - Byte
DateViewed - Date/Time stamp

or

StatsID - Autonumber - PK?
AdID - Long
DateViewed - Date only
LocationNo - Byte
TimesViewed - long

Categories
CatID - Auto - PK
Category - text 50
IsActive - Y/N (default Yes)

SubCategories
SubCatID - Auto - PK
SubCategory - text 50
IsActive - Y/N (default Yes)


As to how you get the 5 ads, I guess it could be disconnected recordsets, held in a session, or an array held in the session for each of the ads.

Hope that helps a bit

The stats would be an update sql statement. I don't know if it would be best to call as you send the page back (as you choose the visible ads) (thinking in Asp terms) and whether the layout is the best way.

Also you'd need to consider the size of data, the stats would take up the most I suspect depending on how you do it.


Vince

[Ecniv]

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.

Right ok .. not the session then, possibly the application level of Asp or the equivalent in php etc...

The only other way would be to have the lists of adverts set say at 1am every day, and then rotated as applicable as you post the page to the user. (start a loop at a different place each time)

You might be able to use a session object to hold where to start viewing the ads from in the table reather than filtering for each user and holding open a recordset.

Vince

[brucevde]

Thanks. Your sample design is pretty much the same as mine. I have one additional table. Since ads can be placed in more than one category/subcategory I included

AdvertisementCategories
CatId
SubCatId
AdId

Selecting the data is not a problem. The problem is updating the "View Order" every time a recordset is returned. For instance if a user were to click on a subcategory, which returns data for all possible Levels (at least 5) - I need to run 10 update statements. An update for each Level for both member and non-member advertisers. Although, each update only modifies a single row, I need to use the same query that fetches the records as part of the update statement (with a Top 1 clause).

I

[Ecniv]

Use a session object for the user to store how far through it is.

So on their first view the code detects nothing, so sets to 1, then reads the top 5.
On the second view it adds 1 to the held value then reads the 5. If there are less than five it loops back to the start.

Example

Pass   StartAt       Returned     Selected/Shown
1       (null)->1    1,2,3,4,5,6   1,2,3,4,5
2       2            1,2,3,4,5,6   2,3,4,5,6
3       3            1,2,3,4,5,6   3,4,5,6,1

Don't know if this is a good way of doing it, but might be worth a try.

As previously posted, you could hold the order in an array (session) to stop/slow the database hits... since you are having stats hits each refresh too.

Just one way I guess.. good luck with it.

Vince