DB rationalisation - sports team database

[pottersdt2k]

Hey guys,

Currently working on improving a fansite for a soccer/football team I've got going at the minute, and was wondering if someone could help me with normalising the database.

Basically, the problem I'm having with the normalisation is with one particular section. This is adding a fixture result to the database, with the following information to be included in the result 'report' (to be displayed on screen)...

The result (eg Win 2-0); Attendance; Home or Away; Stadium; Opposition team name; Kick off Time; Competition; Referee; A list of Goalscorers (for both teams)
plus
A full list of players taking part (for my team, total of up to 25 players), which will include:
Surname, First Name, Time On, Time Off, Yellow Card (yes/no), Red Card (yes/no), Rating (1-10)

but I also want the user to be able to search the database for certain items, with the following outputted as a 'report';

A list of fixtures when a player has scored
A list of fixtures when a player has received a red or yellow card
A list of fixtures a player has taken part in
A full list of results against a particular opponent based on team name
as above based on stadium, home or away
A player biography to be shown based on a players surname or first name (result displays a list of links to the profiles of matching seach results)
List of fixtures controlled by a certain referee
other searches to be determined at a later date...


So far, I have the following tables which I think should work.

PLAYERS TABLE
PlayerID (Key), Surname, Forename, DateofBirth, Nationality, DateJoined, DateLeft, Position, ValuePurchased, ValueSold, Biography, Picture

GAME TABLE
GameID (Key), OppositionTeam, HomeOrAway, Stadium, KickOff, Competition, Attendance, Referee


Now, I aren't sure how to link the Players and Game tables together to create the player lists and goal lists...

Also, I presume that this should be done in SQL - or at least that would be the preference - but I'm more experienced in using MS Access; does anyone have any advice on which technology to use for this project? I will be using SQL for a phpBB forum anyway...

Thanks for any help you may be able to contribute; I realise this is a complicated issue (:eek: ), and am most grateful for any help offered :)

[J.Karlsson]

I'm actually trying to do something similar to you. I have a table called Lineups with the fields GameID, PlayerID, TimeOn, TimeOff, YellowCard and RedCard. As for the goalscorers... I only display "my" team's scorers (with the help of a Goals table with the fields GameID, PlayerID and GoalMinute). I don't know if it's the most efficient way, but I haven't figured out a better way to do it.

[pottersdt2k]

J.Karlsson, I've got an hour or so now, so I'm gona sit down and blitz this to see if I can crack it... the only ways I've come up with so far have quite a lot of redundancy in them, and even my College lecturers couldn't come up with a suitable solution.

I'm sure it'd be possible though somehow... will post my results up if I work it out.

[NogDog]

I might approach it along these general lines:

player table
=========
(various more-or-less static fields that describe each player)

referee table
=============
(similar to player table)

team table
===========
(more-or-less static data about each team)

player_to_team table
====================
player_id (from player table)
team_id (from team table)
(possibly other fields such as "date_joined" and "date_departed" to keep track
of current status and history)

fixture table
=============
fixture_id (auto-increment)
location
date/time
home_team_id (from team table)
away_team_id (from team table)
referee_id (from referee table)
home_team_score
away_team_score

goal_scorer table
=================
fixture_id (from fixture table)
player_id (from player table)
game_time

card table
==========
fixture_id (from fixture table)
player_id (from player table)
card_type (yellow or red)

The overarching concept here is that the items which are variable in number and of a more dynamic sort (e.g.: team rosters tracked by the player_to_team table, goal scorers, and warning cards) are tracked in relation tables that link these items to the more-or-less static data tables. Of course, the fun then starts when you go to write your queries to extract some of the reports, as you'll need to joing multiple tables together; but that's how you'll extract the most power and flexibilty out of the whole relational database thing.

[pottersdt2k]

The solution I came up with last night has a total of 10 tables... they are listed below, but it should be noted I made this in MS Access, although I think it should be capable to transfer the idea and design over to a MySQL database....

The tables are:
Competitions, Game, Goals, Lineups, Managers, Opposition, Players, Referees, Stadium and Venue...

COMPETITIONS
CompetitionID, CompetitionName

GAME
GameID,OppositionID, StadiumID, CompetitionID, RefereeID, ManagerID, HomeScore, AwayScore, Venue, KickoffTime, Attendance

GOALS
GoalID, GameID, PlayerID, GoalMinute

LINEUPS
LineupID, GameID, PlayerID, YellowCard, RedCard, TimeOn, TimeOff

MANAGERS
ManagerID, manager details

OPPOSITION
TeamID, team name and logo

PLAYERS
PlayerID, players details, biography etc

REFEREES
RefereeID, referee details

STADIUM
StadiumID, capacity etc

VENUE
Just Home or Away

NogDog, any idea where I can get a tutorial on how to link the multiple tables together for the reports? I'm a novice to PHP and SQL - I've got a feeling I've taken too much on with this project!! lol

[J.Karlsson]

Is the Venue table necessary? I mean, since you have the venue field in the Game table, I'm thinking that it won't be difficult if you for example wanted to get a list of all home games - you just have to search the Game table. Another thing: how do you determine who played from start and who started on the bench? You might want to consider adding a Substitute field to the Lineups table (it could be a yes/no thing, or 0/1 or whatever).

By the way, I'm not NogDog, but there are PHP/MySQL tutorials at http://www.php-mysql-tutorial.com and http://www.freewebmasterhelp.com/tutorials/phpmysql

[pottersdt2k]

J, I was thinking that by using the "TimeOn" field, you could determine who was a substitute and who started...

If someone started the game, TimeOn would be 0 minutes, otherwise a substitute would be for example 88 minutes? Although now I realise that someone who was a substitute but didn't play (i.e. an unused substitute) wouldn't display correctly using that method, so I guess a check-box could be used.

I guess the tblVenue isn't necessary... I was wondering if it would cut down on the repeating data, but I aren't sure that it would...

Thanks for the tutorial links - I'll go check them out... But just found out that my web server has suddenly disappeared on me so think this is gona take a while to sort out properly...