Advice with tables

[michaelsmith]

Hi, I am in the process of designing a new database driven site useing php and mysql. I am fairly new to these and would like to through myself in at the deep end in order to learn.
It is to be a fantasy football league.

I will have 4 tables which look like this:

Personal
_______________________________________________________
| ID | Username | Email | Support | Password | Datejoined | TID |

Team
____________________________________
| ID | Name | Money | Transfers | Points |

Player
_____________________________________
| ID | Name | Team | Value | Pos | Points |

Lookup
__________
| TID | PID |

The players points will be updated manualy.
My problems are, I want the teams' points to be updated by the players points. How can I achieve this.

My first thought was to have each teams total worked out when that teams user logged on by adding up the points of each player in his team. This will not work as each team has 17 players and only 11 active at one time. These can be changed so that that the team would not be due to every point earned by each of its players. Also I would like to have a system of transfers which would mean that some players would only be part of a team for part of the season.

Also I would like to have the teams total points available each week to have a league table. I think the updating of teams points need to be updated when ever I update any players points but I am not sure how this would work.

Please note I am not looking for any code, just a bit of direction. Do my tables look feesable? Is there an easier or better way to aproach this or am I basically on the right track?

Any advice would be greatly apreciated
Lee.

[Defender1]

well, this may just be me, i wouldn't use ID for all of those tables. i always like to use fairly unique names for my tables.
expecially for joining tables (which you'd have to do to interact between the the player/team tables to get totals etc.
now, normally when joining, you'd do something like this:

*Note that the first column is the key (must be unique and not null btw)
Employees
_______________________________
Emp_ID | Rate | Hours | etc...

Payroll
_______________________________
Pay_ID | Emp_ID | etc...

in order to join those two, you'd link via the Emp_ID.
but Emp_ID would always carry the same info.

i know that's a bit to take in, so unless you want the same info in a different table, use some unique column names.

[freakysid]

Let's start off from the point of view of what is the relational theory tells us is the right approach.

Team.Points is redundant and breaks data normalisation so it should not be in your normalised design.

You say that the problem in working out team points is that it is the sum of the active players points (which will be 11) rather than all of a team,s (17) players. So your data model needs to capture whether a player is active on the team or not. This should be added to the table Lookup.

Lookup(tid, pid, active)

The mysql query for this would be:

CREAT TABLE Lookup (
tid INT UNSIGNED NOT NULL,
pid INT UNSIGNED NOT NULL,
active CHAR(1),
PRIMARY KEY ( tid, pid ) 
);

With my modification in mind - if I understand you correctly - then to work out the total for $teamName you would use the query:

SELECT SUM(Player.Points) AS TeamPoints
FROM Player, Team, Lookup
WHERE Player.ID = PID
AND Team.ID = TID
AND active = 'Y'
AND Team.Name = '$teamName'

Well that's how you would select the TeamPoints for one team. To create your team league table you would use this query...

SELECT Team.Name AS TeamName, SUM(Player.Points) AS TeamPoints
FROM Player, Team, Lookup
WHERE Player.ID = PID
AND Team.ID = TID
AND active = 'Y'
GROUP BY TeamName
ORDER BY TeamPoints DESC

Now back to your original idea of holding the team points in a seperate field of table Teams. Like I said, this breaks data normalisation and what I have outlined above is the way you should do this in theory.

If executing those queries in real time becomes too inefficient then you can look at "caching" the team points in a seperate field per your original design. Here you would run one of my queries from above and then in your PHP use the value in the result set for each team to in a seperate UPDATE query to update Teams.Points . But, unless you are dealing with massive tables then I don't think you will have a problem executing the joins in the queries I have given above in real time.

[michaelsmith]

Hi, I can see the advantage of adding the extra column, active in the lookup table and this will work. As for the rest I am not sure.

Team.Points is redundant and breaks data normalisation so it should not be in your normalised design.

I do not understand this. If you could simplify it I would be grateful

Also the sql query you have used to work out the team points looks ok although I have never come accross the syntax

SELECT SUM(Player.Points) AS TeamPoints

but from what I understand (I may be wrong) This is not quite what I am looking for.

How I thought it would work would be for each player that had scored any points since the last update would be given whatever points they had made or lost. These points would then be tempararily put into the player table under points. Once all the player points were in place then All the teams in the database would need updating.

I.E. Team points + active players points within that team = update team points.

From here it would just be a matter of sorting teams by the highest no of points to work out the league table.

If this model would work, how would I update all of the teams in one go. Would there be much difference if I was updating 10 teams points or 10,000 teams points.

Thanks
Lee

[freakysid]

OK - I don't think I understand properly how a team's points are made up. I originally suggested that Teams.teampoints was redundant because this information could be derived according to the sql I had given - which was how I had understood that teampoints were calculated. That is, a team's points is the total of all the active players on that team's points. But I'm now thinking that what you are saying is that a teams points at the end of period n will be the team's accumulated points up to the end of period n-1 plus the points of the players that were active on the team during period n.

See I don't fully understand the relationships in your data model and exactly what you are trying to model. Can a player belong to one and only one teach (for the duration of each week) or are you running some sort of fantacy team system where the one player might belong to many different member teams?

I think the approach I would take is to view things more from a week to week perspective. The event of a week seems to be important. A player will be on a team for each week. A team will have a certain number of players each week. A player will score so many points each week. A team will accumulate the sum of all its players points each week. So I would break it up into weeks more - kinda, if you get what I mean (if I get what I mean )

Anyway, here are some thoughts - but they are off the top of my head - without sitting down and drawing up an entity relationship diagram or thinking this through a lot.

player(id, name, ...)

team(id, name)

week(week_number, ending_date)

player_weekly_points(week_number, player_id, player_points)

active_team_player(player_id, week_number, team_id)
[constraint] each team_id can only have X number records with the same team_id (each team can only field so many players each week).

Optional table

team_weekly_points(week_number, team_id, team_points)

Some queries:

1) Update a player '123' point's for end of week 5

INSERT INTO player_weekly_points
SET week_number = 5, player_id = 123, points = 100

2) Work out the team 444's points for week 5:

SELECT SUM(player_points)
FROM player_weekly_points, active_team_player
WHERE player_weekly_points.player_id = active_team_player.player_id
AND player_weekly_points.week_number = active_team_player.week_number
AND team_id = 444
AND week = 5
LIMIT 1

3) To work out a team's total points for all weeks the long way (without caching in a table team_weekly_points)

SELECT SUM(player_points)
FROM player_weekly_points, active_team_player
WHERE player_weekly_points.player_id = active_team_player.player_id
AND player_weekly_points.week_number = active_team_player.week_number
AND team_id = 444
LIMIT 1

4) Or if you do use the optional team_weekly_points table to cache weekly results then working out team 444's total points for all weeks:

SELECT SUM(team_points)
FROM team_weekly_points
WHERE team_id = 444
LIMIT 1