Banner Impressions Tracking - Database Design

Question

Hi Folks

Looking for some good advice on db design for tracking multiple banner impressions.

IE I have 5 Banners over x Domains

I would like to build data on each banner on how many impressions per day per banner etc. So also be able to do lookups for other date ranges.

Would it be best to have a date per day per row or just track each impression per row.

Hope you can advise.

And thanks in Advance

Answer 1

I'd recommend to create the most flexible design that would allow you to create new reports as requirements extend in the future. You suggest that the customer wants reports on "impressions per day". What if they come in later and say "what time of the day are impressions shown most at"? How about "when are they clicked on most"?

So the most flexible way to do this is to have 1 record for each impression, where each record is just banner_id timestamp

Later on, you can create a stored procedure that aggregates historical data and thus purges HUGE amounts of data that you have accumulated - thus, creating reports on the level of granularity that you care about. I can imagine storing hourly data for a month, and daily data for a year. The stored procs would just write to an archive table:

• Banner ID
• Time interval identifier (of the month/year for monthly data, or day/month/year for daily data, etc)
• Number of impressions

Answer 2

Why reinvent the wheel? There are plenty of free ad servers. The most notable one I've heard of is OpenX (used to be phpAdsNew). If nothing else, you can install it and see how they set up their DB.