Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: Little bit of a hard time planning this database structure

    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 (http://www.dbforums.com/showthread.php?t=1609646 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
    Last edited by ianq86; 11-26-07 at 19:32.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ianq86
    I've read an excellent synopsis of a friend network structure way back (http://www.dbforums.com/showthread.php?t=1609646 Read it. It's great!)
    thank you very much!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •