DB to store games and results

[The_Hobbit]

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?

[TravisVOX]

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.

[Maverick22]

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)

[Maverick22]

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"

[jgilmartin]

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.

[Maverick22]

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?

[Blax0r]

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.

[jgilmartin]

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.

[Maverick22]

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

[The_Hobbit]

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

[HeeeHAWWWW]

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.