CODEWITHSUNDEEP
phpmysqldatabase-designschema
Lee
Lee

Reputation: 20934

Banner Impressions Tracking - Database Design

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

Upvotes: 1

Views: 1623

Answers (2)

ceejayoz
ceejayoz

Reputation: 180004

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.

Upvotes: 2

Alex Weinstein
Alex Weinstein

Reputation: 9891

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

Upvotes: 3

Related Questions