SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advice with tables

    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.

  2. #2
    SitePoint Wizard Defender1's Avatar
    Join Date
    Apr 2001
    Location
    My Computer
    Posts
    2,808
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Defender's Designs
    I'm Getting Married!

    Not-so-patiently awaiting Harry Potter Book 7 *sigh*

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    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...
    Code:
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Location
    North East, England
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by freakysid; Sep 10, 2001 at 07:44.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •