solid7 — 2013-10-01T23:52:40-04:00 — #1
NOTE - This will be an ACCESS DATABASE
OK, this might be a big task for me to tackle at such an inexperienced level - but I'm gonna do it, anyway.
I'm putting together a sports database. I have compiled a multitude of stats, and now I need to arrange them. Here's what I'm starting with...
I have 10 Excel Spreadsheets, representing 10 years of League play. Every year, obviously, has its own figures. Not all players play in all years. Not all players play on the same team in every season. Not all players have unique names. (there are a few with the same name, same spelling)
I have NO IDEA where to begin on this one. I am reading up on things like "primary keys", and "surrogate keys", and other things which have already made this daunting. My assumption is that I have to design my database with the query in mind. But that puts me into a continuous loop of confusion.
So here are a few of my fields. These are soccer stats:
Player | Team | Position | Games Played | Minutes Played | Goals Scored | Yellow Cards | Red Cards |
Now, this all has to be repeated for every season
I am assuming that because there can be more than one player with the same name, some other ID needs to be introduced, here? Is that correct? Because really, I have no other way to distinguish which player is which. (especially with the possibility of a team swap)
I'm going to stop typing for a minute, and start to take the feedback. For which, I thank you in advance...
spacephoenix — 2013-10-02T01:42:17-04:00 — #2
You need to break it down into a number of tables, some will represent an entity such as a player or game and some will be "linking" tables. A Few ideas for tables:
Player: Each record represents one player, with fields for their name, gender, date of birth, date they joined the league, etc
Team: Each record represents one team with fields for things like, team nickname, mascot name, ground/pitch location, manager, etc
Player-Team: Records when a player joined a team and when they left a team
Manager: Records details about a teams manager (maybe known as a coach in some sports)
Manager-Team: Basically the same as Player-Team but for each manager
Can you please post a complete list of the column headings?
Have a read of these two wikipedia articles:
solid7 — 2013-10-02T09:06:49-04:00 — #3
Definitely understand this, and am on the same page. Just very confused how to accomplish it.
For now I would like to assume that I have given you the complete heading. I want to construct a simple database, based on only the data that I've given so far. It actually encompasses all of my difficulties. All of the rest of the columns are statistics similar those shown to right of "Position", and the list is HUGE.
Thank you for the links.
solid7 — 2013-10-02T12:37:00-04:00 — #4
Hopefully, this gives a better clue what I want to accomplish....
Here is what I hope to achieve with the database:
Whereas this is what I have right now: (representing one spreadsheet for every season)
Player | Club | Apps | Goals
Player_Name | club_1 | x | x