Hello, everyone. This is a nice forum, and I've not had great luck asking questions elsewhere, so I'm going to give it a try here.
Just a bit of background - I am not a programmer, and I will not be using databases professionally. A project has come up that captured my attention, and it's a hobby that interests me, so I want to learn something new.
My database will be a sports (specifically soccer) statistics database. Currently, I have 10 tables, representing 10 seasons worth of statistics. Now, I have a huge number of statistics, but I can start with a simple database, and expand on it. I'm having the most trouble figuring out how to make the data relational across this range of years.
First, what I want to do:
What I actually have: (this represents one a single year - I have 10 individual tables)
Player | Club | Apps | Goals
------------------------------------------------------
Player_Name | club_1 | x | x
I have been reading up on relational database design, and normalization. While I understand, for the most part, what I guess eludes me, as such a stark newbie, is the issue of the seasons. For some reason, I keep getting hung up on this.
I think I need some tables that look like this:
Player
------
Name
DOB
Height
Weight
Position
-------
Striker
Goal Keeper
Center Mid
Fullback
Etc
Season
-------
2005-06
2006-07
Etc
Team
-----
Club Name
Statistics
---------
Goals Scored
Yellow Cards
Red Cards
Etc
I'm also guessing that there needs to be some other ID (I guess this is a surrogate key?) for the Player name, and some players could potentially have the same name.
That's a lot of info so far, so I'll throw it out, and see where it comes up.
Thank you in advance.