SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Franklin, IN
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Schema Advice

    Greetings all. I've been reading Kevin Yank's book, "Build Your Own Database Driven Website," and I had a database organization question.

    For years I have run a small prognostication contest for about 35-40 fellow fans of a college football team. Basically the way it works is they sent me their best guess on the final score of each football game during the week leading up to each game. They also send me our team's passing yardage. I plug those numbers into an Excel spreadsheet or a computer program I wrote, and then I compose an email to show the updated scores each week and then a final score at the end of season.

    Needless to say, this little contest just screams for a database, so I've decided to give it a whirl -- to do the whole thing online, let the users log in and submit scores, keep running totals, send automated emails with the standings, etc.

    Anyway, I understand that it's terribly important to get the tables set up right to facilitate the types of searches and computations that I will need to do on the data. Here is what I have thus far:

    Table #1: Contestants
    Columns: ID, Name, Email

    Table #2: Picks
    Columns: ID, MyTeamPick, OpponentPick, YardagePick, CID (contestant ID), GID (game ID)

    Table #3: Games
    Columns: ID, Season, Opponent, GameDate, GameTime, MyTeamScore, OpponentScore, Passing

    Now, here is the $64 question and where I could use some advice. I will obviously want to be able to compute the current results. While the three tables above have all the information I would need to figure that, it sure seems like it would be awfully processor intensive. It will essentially mean going through every record and running an algorithm on it to ascertain the score for each contestant and then adding the totals for each user before displaying anything. 35 contestants x 12 picks is over 400 picks records a year... no, that's not that many, but I guess I am just wondering if it would make any sense or be more efficient to create a 4th table called results which would be a running point and stats total for each user, which could be very quickly queried when someone wants to see the standings.

    Any comments or advice on this would be appreciated. Thanks in advance!

    Scott Henry

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Without answering your questions directly as to which way would be the best to go, let me offer some considerations.
    1. PHP can crunch through an awful lot of data before performance starts to degrade with regard to loading your web page. Unless you had several thousand database records to go through in order to present your results, you'd probably be better off without that summary table you're talking about doing.
    2. If you went with your summary table, you would need to ensure that it agreed with the detail data that supports it. If it ever got out of sync with the detail data, you'd have a data integrity problem. OK, maybe that's an unlikely event but still something to think about.
    3. Will you need to search through the summary data to look for say, averages of something over a certain amount? If so, it could be pretty labor intensive to present such a list without your summary table.

    In the end, I guess it all depends on how you want to pull data out of your database and how easy it is to do that with your table structure.

    Hope this helps.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web


Bookmarks

Posting Permissions

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