Database Newbie - Need Some "Get Up to Speed Quickly" Advice

[solid7]

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.

[June7]

The yearly tables should be combined into one table (Stats) with another field for the Season attribute. This will greatly simplify your input and output of data.

Yes, use a PlayerID field in the Stats table instead of player name. This ID can be generated by autonumber type in the Players table. Updating a field in Stats could be simple if there aren't players with same name.

Player name parts should be in separate fields in Players table (FirstName, LastName, MiddleName).

Advise no spaces, special characters/punctuation (underscore is exception) in field or object naming and no rerserved words as names.

Otherwise, you are off to a good start.

[solid7]

The yearly tables should be combined into one table (Stats) with another field for the Season attribute. This will greatly simplify your input and output of data.

Yes, use a PlayerID field in the Stats table instead of player name. This ID can be generated by autonumber type in the Players table. Updating a field in Stats could be simple if there aren't players with same name.

Player name parts should be in separate fields in Players table (FirstName, LastName, MiddleName).

Advise no spaces, special characters/punctuation (underscore is exception) in field or object naming and no rerserved words as names.

Otherwise, you are off to a good start.

June7, thank you for your input.

I didn't separate first, last, and middle names. For what I'm doing, I didn't think it prudent. (seems like separating first and last names causes me more problems than it's worth) Plus, I don't know middle names, and I also have a unique field that identifies players, which I didn't mention before. Figured I'd need it to differentiate, as this will be going into an SQL database, later on. Your input on the spaces is well taken, I was just breaking down the schema in the above example.

So, if I understood everything correctly, here is a list of tables that I should probably be pursuing:

Player
------
Unique ID (PK)
Name
DOB
Height
Weight


Position
-------
Striker
Goal Keeper
Center Mid
Fullback
Etc


Team
-----
Club Name

Statistics
---------
Season (PK)
Goals Scored
Yellow Cards
Red Cards
Etc


Thanks again....

[ItsMe]

Hello, everyone. This is a nice forum, and I've not had great luck asking questions elsewhere, so

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.

You need a table to store activities. You will probably need this activity in another table. You might be able to use the Statistics table.

Every time a player creates a statistic, you add a record to the statistics table. The statistics table would have a foreign key field that represents the player ID from the player table.

The problem is that your tackling a huge amount of data. After you identify another "something" or "activity" that will create many records, you will need another table for that. You will need to draw relations for this new activity you identified. The DB becomes more complex. It is hard to take something like "statistics" as a starting point to CREATE a data base. Statistics are more like results you would get from a data base.

If you were starting out and decided to create a DB. You might be better off choosing something simple and not very dynamic. Maybe purchasing gasoline. Gasoline ties into many things in our lives but it would not be hard to isolate a specific activity and record that activity into a DB.

On Tuesday I purchased gasoline from Station "A' that is Fuel Grade "B".

The activity of purchasing has a few relations that you can isolate and easily place into just a few tables. You got a Fuel Grade, locations, and yourself. Build a table for the activity and place the Primary Keys of the relative records in the activity table's foreign key fields. That will easily illustrate relationships.

After a while you can give a copies to your friends and they can track their fuel purchases. After a season, I mean year, you can collect the DB's and merge them to create statistics.

[solid7]

The activity of purchasing has a few relations that you can isolate and easily place into just a few tables. You got a Fuel Grade, locations, and yourself. Build a table for the activity and place the Primary Keys of the relative records in the activity table's foreign key fields. That will easily illustrate relationships.

After a while you can give a copies to your friends and they can track their fuel purchases. After a season, I mean year, you can collect the DB's and merge them to create statistics.

I'm not sure I follow you here... As I said earlier, I already have 10 full seasons worth of statistics in excel spreadsheets. Once the database is properly set up, it will be linked to site used by my statistics keeper. (his input will automatically be linked to the fields of the database) So, I'm not really "extracting" any statistics. I'm simply offering a way to query and display the results of what has already been collected. (per my table, first post)

[June7]

Not separating name parts can make sorting and filtering more difficult. At some point probably need to sort/filter by lastname, firstname. So unless you input names in that pattern, gets complicated. It's easier to combine data than break apart.

[solid7]

Not separating name parts can make sorting and filtering more difficult. At some point probably need to sort/filter by lastname, firstname. So unless you input names in that pattern, gets complicated. It's easier to combine data than break apart.

Fair enough. It would be simple to write a macro to break them out programmatically, since I've already amassed the data.

I'm afraid that I'm a little baffled at how the Season fits in with the table that holds the Stats...

[ItsMe]

I'm not sure I follow you here...

My opinion is that you have bitten off much more than you can chew. You have the cart before the horse. There is not any light at the end of the tunnel.

What I am suggesting as a solution to your problem and an answer to your question on how to build relations is to practice with something small.

Ten years of statistics is very dynamic. You are approaching it from the results side of things. If I were setting off to accomplish the goals you have set, I would break off small pieces to see how the statistics were CREATED. I would submerge myself in the different activities that cause the statistics. With that knowledge and my understanding how relations work. I would start to build a matrix, one section at a time.

[solid7]

I would break off small pieces to see how the statistics were CREATED. I would submerge myself in the different activities that cause the statistics. With that knowledge and my understanding how relations work. I would start to build a matrix, one section at a time.

Well, maybe this is a case of putting the cart before the horse. But I really don't want to create a database that has to do with selling things, first. I tried that, and it just confused the hell out of me. I am not making a career out of database design, and so, unfortunately, I'm trying to use applied logic. If you have a resource - such as a template for compiling sports statistics, that would be great. I'm not above starting there.

As for how the statistics are CREATED, that's really simple. You turn on the TV. A guy kicks a ball into a net. It's a goal. Mark it down. He gets a red card. You mark it down. Not sure what you mean by 10 years of statistics being "dynamic". On the contrary, once it's on paper, it seems pretty static, to me. I'm simply trying to catalog the data for easy retrieval.

Yes, it's a lot of data. I can respect what you are saying about learning in bits and pieces. But as I said, I want to be direct, and use an apples-to-apples approach. I have not found any of the tutorials that I've done to be helpful here. Maybe it's just that this isn't my native thinking. I'm a designer by trade, not a data compiler.

Thank you for your replies. Your time and help is appreciated.

[ItsMe]

I believe you already have the knowledge of the game and what statistics are. Now you need to understand some basics in relational databases. With that, your issue in post #1 will be closer to and able to be resolved.

Your understanding of the game may actually hamper you. The game is simple. Statistics are here. I have them in spreadsheets.

It is simple because you understand the game forwards and backwards. What I am suggesting is you practice on something basic and small to achieve the second component. Develop an understanding of relations. With both components in hand you will likely save time and energy in the long haul.

I could be mistaken. At least take June7's advice and get your First and Last name fields in order. Using naming conventions to identify different objects. Avoid special characters and spaces between names. You posted a broad question. I am trying to offer a place to start

[June7]

You said you have 10 tables for 10 years (10 seasons)? I suggest this be one table. If the tables are combined, what will identify the year/season the records pertain to? Hence the requirement for the Season (YearPlayed) field. Call it what you want.

[Dal Jeanis]

1) Do players ever transfer clubs in mid-season? If so, then you may need to include both player and club as PK in the stat records.

2) Are you tracking the actual games, which teams are playing against which teams? If so, then you'll probably want a match table, and the stats would be against the match, rather than the season.

[solid7]

1) Do players ever transfer clubs in mid-season? If so, then you may need to include both player and club as PK in the stat records.

2) Are you tracking the actual games, which teams are playing against which teams? If so, then you'll probably want a match table, and the stats would be against the match, rather than the season.

Ah, there we go... Those are really great questions. You have done this, it seems.

Yes, players can transfer mid-season. So, there is a possibility of playing for 2 clubs per season. And yes, it happens often. As for game tracking, no, I am only tracking individual player statistics.

Thank you for your reply.

[solid7]

Statistics
---------
Season (PK)
Goals Scored
Yellow Cards
Red Cards
Etc

I am having a bit of trouble still with this, in the design stage.

OK, so we know that a set of stats can only apply to a player for one particular season. No problem with that. We know that a query for a season can tally all of the stats for all players for a particular season, or all stats for a particular team for a particular season, so on, so forth. But what's got me chasing my tail, is that if I have included the season in the stats table, how does that work? There is a finite set of values that are available for the "season" field. I can't exactly populate the stats table, because it requires info down to the player level to be meaningful. So that's my confusion. How can I populate just the season, but leave the rest of the table blank? (do we start building relationships in a completely separate table?)

I'm sorry, I see the end result so clearly, but I'm not finding the steps to get there...

Thank you.

[June7]

If you don't care about in which game nor for which team the player played, only yearly totals, consider:

Stats table

PlayerID	Goals	RedCard	YellowCard	Season
1	15	2	5	2011
1	20	1	3	2012
2	8	1	2	2011
3	11	4	1	2012