Hi everyone,
I'm glad I finally got around to signing up at this forum. I've been reading here for ages, but never had to post anything. Now I do and I'm wondering if someone would be so kind as to provide me with feedback. I've read an excellent synopsis of a friend network structure way back (Friends Network (undirected graph) Database Design Read it. It's great!) and this is when I started to really like this forum. Enough of the sucking up, let me get to business

My boss wants me to write a little analytics application for our advertisers. Essentially, an advertiser can create an ad campaign and see a report that shows how many people have clicked and some of the information of the people that clicked on the ads. This is for a portal website that my boss's company is developing (I'm interning at the moment; gotta love Minnesota ). Anyways, this is what I've planned out so far:

Two tables: AA_reports for the main report and AA_extra with cron job updated data.

AA_reports contains some basic information such as the number of total clicks and impressions, CTR, target URL, ad banner filename, you get the picture.

AA_extra is juicy. It contains daily usage information, such as:
count_clicks - INT(7) // holds how many clicks, impressions, male/female clicks have occurred that day
count_clicks_male - INT(7) // holds how many clicks, impressions, male/female clicks have occurred that day
count_clicks_female - INT(7) // holds how many clicks, impressions, male/female clicks have occurred that day
count_impressions - INT(7) // holds how many clicks, impressions, male/female clicks have occurred that day
total_age - INT(10) // adds the ages of all users up; to display average age, divide total_age by count_clicks
date - VARCHAR(10) // UNIX timestamp for the day

Each record in AA_extra contains one day's worth of information per ad campaign active.

Now the tricky part is some additional user info. You can already see that I want to show the advertisers the gender breakdown of the clicks (male/female). In addition to that, I want to store/track/show the following:

Exhibit A:
Degree breakdown (e.g. MBA, BA, BS, JD, etc.)
Language breakdown (e.g. DE, EN, CH, TW, etc. - all the major languages)
Country breakdown (e.g. US, UK, CA, DE, CH, etc. - total of around 230 countries)

Does anybody have an idea of how I can store this best? The breakdowns under (Exhibit A) don't have to be tracked on a daily basis but per campaign.

Any help/feedback would be much appreciated!

Thanks in advance,
Ian

Starting off and without getting into too much details of the particulars of your project, it would be prudent to establish what information is being collected from your site. With this you can design your database structure (in normalised form), and thus generate your queries to produce the information you are after.

At a quick guess I can visualise the following tables:
Advertisers
Advert_Campaigns
Advert_Clicks

The relationships are:
Advertiser 1 - to - many Campaigns
Campaign 1 - to - many Clicks

Some pertinent information on clicks:
1) Each click is made by a user (do you store these user details somewhere?)
2) Each click is made by a male or female (or alien)
3) Each click is made at a date/time
4) At the point in time that the click was made the participant was of a certain age, was in a particular country, and had a degree and spoke a language.

Hopefully now we both have some clarity on the situation.
If you post your DDL for your tables with some sample data i'm sure we can help further.

thank you very much!