Soccer/Football Database Design

Posted on 2005-03-24
Medium Priority
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.
Question by:ammo12kmf
LVL 51

Accepted Solution

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

TeamID  <-- PK

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

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

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


Author Comment

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 :)

Expert Comment

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
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.

Author Comment

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)
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!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

599 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