1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need advise on MySQL table structure

Discussion in 'MySQL' started by jawinn, Nov 19, 2006.

  1. #1
    I'm setting up a DB to house info on football games and have people select which team(s) they think will win each week. Here is the data I will have to work with. I need help in figuring out the best way to setup the table.

    ID field (unique key value set to auto increment)
    Week number (each week will have a different number)
    Team 1 vs Team 2 (teams playing that week)
    Team 3 vs Team 4 (teams playing that week)

    There will be 12 to 14 "team vs team" fields. The winners will be decided by either manual input or through a script. The issue becomes should I divide up the Team 1 and Team 2 into separate fields or put them in a single field?
    SEMrush
     
    jawinn, Nov 19, 2006 IP
    SEMrush
  2. rosytoes

    rosytoes Peon

    Messages:
    230
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Table - Teams
    teamid
    team name

    Table - Matches
    weeknumber (or date/timestamp if you need to know what day and time)
    team1 - foreign key teamid
    team2 - foreign key teamid
    result
     
    rosytoes, Nov 19, 2006 IP
  3. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Will the users be making their selections within the system? If so, you will need a users table and a selection table. I would suggest naming the two columns in your matches table to be home_team and away_team. Also, add a matchID in your match table. Best practice to avoid any error when matching rows if any data was improperly entered.

    The selection table would have:
    selectionID
    userID
    matchID
    choice (this could be a bit field on for home, off for away. Or it could be a char field).

    Users table:
    userID
    name
    password (possibly)
     
    druidelder, Nov 20, 2006 IP
  4. jawinn

    jawinn Active Member

    Messages:
    1,024
    Likes Received:
    32
    Best Answers:
    0
    Trophy Points:
    88
    #4
    Excellent point druidelder. How would you suggest I work out how the users are shown who picked which games correctly?
     
    jawinn, Nov 21, 2006 IP
  5. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Do you want to show the entire ranking or just the winner?
     
    druidelder, Nov 21, 2006 IP
    jawinn likes this.
  6. jawinn

    jawinn Active Member

    Messages:
    1,024
    Likes Received:
    32
    Best Answers:
    0
    Trophy Points:
    88
    #6
    Both would be awesome.
     
    jawinn, Nov 21, 2006 IP
  7. druidelder

    druidelder Peon

    Messages:
    286
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Here is how I wrote it out on paper:

    tblUser
    -userID int pk (primary key) autogenerate
    -userName char(25) [or varchar if you have it]

    tblTeam
    -teamID int pk autogenerate
    -teamName char(25)

    tblMatch
    -matchID int pk autogenerate
    -homeTeam int fk (foreign key) [teamID from tblTeam]
    -awayTeam int fk [teamID from tblTeam]
    -matchWeek int (must have some way to determine which matches to calculate)
    -homeScore int
    -awayScore int
    -matchWinner bit (boolean) [true for home, false for away]

    tblSelection
    -selectionID int pk
    -userID int fk
    -matchID int fk
    -choice bit

    Those are the tables I would have for the query below. This set up is simplistic and will only work if you're picking winners and not scores. Also, the tables would need to be cleaned up or the weeks would have to ever increase if you used it over multiple seasons. If you added a matchYear column to tblMatch then you could avoid this.

    Query:

    SELECT u.userName, (SELECT count(s.*) AS pickCount FROM tblSelection AS s INNER JOIN tblMatch AS m on s.matchID = m.matchID WHERE s.userID = u.userID and m.matchWeek = @week and s.choice = m.matchWinner) AS wins FROM tblUsers ORDER BY wins DESC

    This gives you the list from the top to the bottom. @week is a parameter that is passed in (or hard coded, but that would have to be changed each season). You can do a Select Top 1 to get the winner.

    I didn't run this, so there may be a syntax error or two, but you get the general idea. This only gives you totals, not a list of the matches with picks/wins.
     
    druidelder, Nov 21, 2006 IP