?
Solved

Soccer/Football Database Design

Posted on 2005-03-24
7
Medium Priority
?
2,963 Views
Last Modified: 2012-06-22
Hello all,

this is more of a database question as opposed to a specific access question but I hope someone is still able to help me?

I am putting together a little database of my local soccer/football team.  All i want to store is information about each match and player information.  Some of my queries I would like would be how many games a player has played, scored, assisted etc..

I am just struggling on table setup and how I would create the queries.  Im mainly unsure if i should store the player stats in the player table or create queries that would extract the information from the match tables?

Any help would be greatly appreciated.

Many thanks in advance, Alan.
0
Comment
Question by:ammo12kmf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 13626263
This should get you started:


tblTeams
---------
TeamID  <-- PK
OtherFields

tblPlayers
----------
PlayerID  <-- PK
TeamID  <-- FK to tblTeams.  I assume each player can only have one team?
OtherFields

tblGames  (this setup requires 2 records for each game)
--------
GameID  <-- PK
TeamID  <-- FK to tblTeams
HomeOrAway
OtherFields

tblPlayerAction
---------------
PlayerActionID  <-- PK, autonumber
PlayerID  <-- FK to tblPlayers
GameID  <-- FK to tblGames
ActionType  <-- could be a lookup
OtherFields

0
 

Author Comment

by:ammo12kmf
ID: 13626329
I understand where your heading BUT how would I have a query that shows:

player name    games played   games won      game lost       game drawn   scored
alan                    4                     2                    1                     1                 6

sorry these are such basic db questions I ma just trying to understand how it would all interact :)

many thanks for your help so far :)
0
 
LVL 9

Expert Comment

by:stengelj
ID: 13626598
Create a crosstab query.

PlayerName where Crosstab =  Row Headings <-- This would be in tblPlayers
Action (Played, Won, etc.) where Crosstab =  Column Headings <--This would be in a lookup table (like tblActionTypes)
ActionType where Crosstab = Value and Total = Count <--this would be in tblPlayerAction

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 13627756
My guess is that more than a couple of people have created this before, and to search download.com and google.com to see if you can find any.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13630266
For how many goals scored:

Say your PlayerAction #6 is scoring a goal:  SELECT PlayerID, Count(*) FROM PlayerAction GROUP BY PlayerID WHERE PlayerAction = 6
For games played:  SELECT PlayerID, Count(*) FROM Players INNER JOIN Games on Players.TeamID = Games.TeamID GROUP BY PlayerID

Games won, lost and drawn will be similar to that.  And yes, you can combine the queries anyway you like...that's the beauty of SQL.
0
 

Author Comment

by:ammo12kmf
ID: 13632867
I must admit at first I wasnt sure where it was all going but a little research and all became a bit clearer.  I have seen other places have hundreds of tables (one for yellow cards, one for goals, one for red cards etc...) but your action idea sounds just the ticket ;)  All i got to do is work out how to put the sql queries together :) (Will be in MySQL at the end LOL)
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13644029
The queries can get complex, but it will be a lot easier since you're starting with something resembling a proper design.  If you need any more help, we'll be here.  :)  Good luck with the rest of your project!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question