I would like to create a Mysql database containing schedules for major league baseball teams. I am trying to figure out the best way to design a table so that each teams schedule is included, but when selecting all of the games for the current date, duplicate data is not retrieved. For example columns would likely be: month, day, team, opponent, location I need to include the full schedules for each team, but a game today between Atlanta and New York played in Atlanta would return 2 rows for a single game. 2,19,atlanta, new york, atlanta 2,19,new york, atlanta, atlanta and similarly a select statement to retrieve all games for the next 7 days would show duplicate results for each game.
Have a game table with fields along the lines of: GameID TeamAID TeamBID VenueID GameDate Any date searches would be done against this table to prevent the duplication as you say
Thank you Astoroth, I guess a better way of asking what I'm getting at would be is there a good way to come up with a unique ID for each game without doing so manually, one at a time for each? I would like to set up a table for multiple sports with decades of schedules, so I can't quite figure a way to enter all of the data in a timely manner and come up with a unique game ID for each one myself. What I do to enter data now is make a csv file and populate columns with all of the data for each team then use LOAD DATA INFILE to add it to the database. I would like to eventually have not only schedules for each team, but additionally results and other data. The score for each team, a column with values of W, L, or T for win/loss/tie, H, A, or N for home, away, or neutral site, whether it was a conference or non conference game, a division or non division game, etc. Here is an example of a site someone else has made for professional baseball. If you scroll down to the game results, I would like to be able to store data like this for each game for many teams. http://www.baseball-reference.com/teams/NYY/2007_sched.shtml
The easiest ID type is simply an "auto number"/ identity column so it is just a sequential number though there are other options. db design is a fairly complex topic if you are talking professional, scalable etc and you get people who do nothing else other than design structures to get the best results.... typically there are a thousand different ways to cut the data and therefore how to design the db but depending on exactly what you are wanting the site to be able to do will dictate which is the best way to do it. Unless frequent bulk uploading data is going to be a regular feature it will be better to have pain now adding the data than to have a slow site because you have optimised for upload rather than viewing.