1. #1
    The_Hobbit
    The_Hobbit's Avatar Become A Pro!
    Join Date: 02-21-13
    Posts: 3
    Betpoints: 30

    DB to store games and results

    I'm working on a small project where I will parse a live XML feed to store college football matchups, including odds, into a database. I then want to subscribe to a live results feed that will also be stored in a database. I want to show the matchups and odds on a web page, and have the results automatically update whether somebody picked the winner by the spread or not. So far, I have one table in my database where I will parse and store the live XML feed containing matchups and odds. Here is what that looks like so far:

    Matchups Table
    Match ID (primary key) (Unique Identifier)
    Away_Team VARCHAR (56)
    Away_Odds VARCHAR (5)
    Home_Team VARCHAR (56)
    Home_Odds VARCHAR (5)


    Does anybody have any experience on how I would store the results portion of this project? Would it be in the same table?

  2. #2
    TravisVOX
    TravisVOX's Avatar Become A Pro!
    Join Date: 12-25-12
    Posts: 30
    Betpoints: 861

    If you're talking about high-level stats or data, then within the same row would be fine. Or, you could create another table called "results" (as an example) with the same ID and store results oriented data there but at this point for you that is probably overkill.

  3. #3
    Maverick22
    Maverick22's Avatar Become A Pro!
    Join Date: 04-10-10
    Posts: 807
    Betpoints: 58

    Since I don't know the structure of the xml document, maybe try this?:

    Assumptions:
    1) The XML matches most of competition XML I've ever seen.
    2) Mysql Database is used
    3) At some point you might want to store odds history for a given matchup
    4) There can be multiple "picks or guesses for a single contest
    5) You wanted a database as close to ideal over as close to “no work at all”
    Note:
    - Use Varchar2 over varchar
    - All varchar's lengths across the entire database/scheme should be either 40 or 1024 characters in length, as best you can adhere to this
    - Use the same number type for all floating (decimal, float, double). I prefer double

    Table: MATCHUP
    MATCHUP_ID INT (The Primary key could be a concatenation of the date and team id's or names)
    DATE (YYYY-MM-DD)
    AWAY_TEAM_ID
    HOME_TEAM_ID

    Table: TEAM
    TEAM_ID (An integer)
    TEAM_FULL_NAME ( Ex. Washington Redskins)
    TEAM_NAME (Ex. WSH)
    Note: (Include whatever other Team Data you Can Gather. Wikipedia is great here

    Table: MATCHUP_ODDS
    MATCHUP_ODDS_ID (auto incrementing integer)
    MATCHUP_ID
    ODDS_DATE (YYYY-MM-DD)
    AWAY_TEAM_ODDS (Should be some type with a decimal, NOT a varchar/string)
    HOME_TEAM_ODDS (Should be some type with a decimal, NOT a varchar/string)
    OVER_UNDER (Another decimal, not a string, not an integer)

    Table: MATCHUP_PICK_HISTORY
    Note: Don't put the actual string of the user's pick, if you can avoid it. I don’t think you should need to)
    MATCHUP_PICK_ID (auto incrementing integer)
    DATE
    MATCHUP_ID
    USER_ID (You May not need this)
    PICK_TYPE_ID (int, foreign key to the MATCHUP_PICK_TYPE table)
    PICK_RESULT (boolean or int)
    COMMENTS (1024 length varchar)

    Table: MATCHUP_PICK_TYPE
    PICK_TYPE_ID
    PICK_TYPE_DESCRIPTION (Enum: Home Team Cover, Away Team Cover, Push, Over, Under)

  4. #4
    Maverick22
    Maverick22's Avatar Become A Pro!
    Join Date: 04-10-10
    Posts: 807
    Betpoints: 58

    Let me know what you think.

    If anyone has any beefs with this, please let me know. I would love some helpful tips, if this layout is "bad"

  5. #5
    jgilmartin
    jgilmartin's Avatar Become A Pro!
    Join Date: 03-31-09
    Posts: 1,119

    For the MATCHUP_ODDS table, wouldn't he need fields for both spread and price? Like:

    MATCHUP_ODDS_ID
    MATCHUP_ID
    ODDS_DATE
    SPREAD
    AWAY_TEAM_ODDS
    HOME_TEAM_ODDS
    OVER_UNDER
    OVER_ODDS
    UNDER_ODDS

    And obviously add two more fields if he wants MLs also.

    Also, assuming the odds on the website will change throughout the week, MATCHUP_PICK_HISTORY would need a MATCHUP_ODDS_ID field also, so the database has a record of which user picked which side at which price.

  6. #6
    Maverick22
    Maverick22's Avatar Become A Pro!
    Join Date: 04-10-10
    Posts: 807
    Betpoints: 58

    Yea... what you said makes sense. Didn't think of that.

    Though you would need to add the spread for both teams and not just one. If I understand correctly the spreads are not always negations of each other, for both teams.

    The Odds History shouldn't be a date, it should be a date/timestamp also.

    But other than that, what do you think?

  7. #7
    Blax0r
    Blax0r's Avatar Become A Pro!
    Join Date: 10-13-10
    Posts: 688
    Betpoints: 1512

    One thing I suggest is adding an index on columns that foreign-keys the matchup_id; as the data grows, the query execution time will be noticeably better.

    Oh also, I would considering making the odds data as a time series, so some type of "order id" may be necessary. The primary key on that table could serve that purpose, but I personally like having the ordering start at 1.

  8. #8
    jgilmartin
    jgilmartin's Avatar Become A Pro!
    Join Date: 03-31-09
    Posts: 1,119

    I don't remember ever seeing a situation where the home spread wasn't just the additive inverse of the away spread, but Pinny does have home spread and away spread as separate in their XML feed, so to be on the safe side, I suppose it does make sense to have separate fields for them.

    I think it's good otherwise. I believe Epoch time is always the way to go in regards to speed, although for my own DBs I use YYYYMMDDHHMM as an INT, simply because it is human-readable (in case I need to manually edit something) and nothing I'm doing requires seconds and the applications are of such a small scale that the speed difference is negligible. But if he is using this on a popular site the time will add up fast.

  9. #9
    Maverick22
    Maverick22's Avatar Become A Pro!
    Join Date: 04-10-10
    Posts: 807
    Betpoints: 58

    OP, have you created the database as we all have noted?

  10. #10
    The_Hobbit
    The_Hobbit's Avatar Become A Pro!
    Join Date: 02-21-13
    Posts: 3
    Betpoints: 30

    Thanks for the insight folks, noting all the ideas out there. Appreciate it

  11. #11
    HeeeHAWWWW
    HeeeHAWWWW's Avatar Become A Pro!
    Join Date: 06-13-08
    Posts: 5,487
    Betpoints: 578

    Quote Originally Posted by jgilmartin View Post
    I don't remember ever seeing a situation where the home spread wasn't just the additive inverse of the away spread
    Over time the juice can change, so definitely worth storing both.

Top