Help with database design - complex relationship

[dhirajkumar41]

I am working on phpmyadmin.

i want to create a system in which users will select 10 players from pool of players and gain points from those 10 players, just like 

fantasy football.

 

So my first solution was

 

tables:

 

users(name,details,team[10],points gained)  and players(name,details,points gained by individual player);

 

but i am not sure how to get that team array in users table.

so i searched for solutions, then somesone suggested about creating one more table team and some thing about foreign keys.

But still i am confused how to do this?

 

and 1 more question should i use phpmyadmin or mysql workbench to do this . right now i have both installed on system but i have never used mysql workbench.

 

pls help. Thanks

 

 

[kicken]

You should have a third table to link up the users to their player choices.

 

create table user_players (
  userId int
  , playerId int
  , primary key(userId, playerId)
);

You would have 10 rows for each user in the above table, one row per player choice.

[dhirajkumar41]

thanks for reply..

tables now are :

 

1)user(u_id, details);

 

2)players(p_id,details,points);

 

3)user_team(u-id,p_id,points gained)

 

but i have a doubt. for all 10 rows in the user_team table u_id should be same for a particular user. 

am i correct? or something more  should be there?  

pls help

Thanks 

[kicken]

Yes, for each user, within user_team you'd have 10 rows where u_id would be the same but p_id would change.

[Barand]

Store the players' points for each player and not for each member of every user's team. That way you only have to add a record for each player getting points in a match rather than updating all the team records.

+-----------+                         +-----------+
|  user     |                         | player    |
+-----------+                         +-----------+
|  u_id     |--+                    +-| p_id      |---+
|  username |  |                    | | playername|   |
+-----------+  |                    | +-----------+   |
               |                    |                 |
               |  +-------------+   |                 |   +--------------+
               |  | user_team   |   |                 |   | player_points|
               |  +-------------+   |                 |   +--------------+
               +-<|   u_id      |   |                 +--<|  p_id        |
                  |   p_id      |>--+                     |  match_date  |
                  +-------------+                         |  points      |
                                                          +--------------+

Then you can the get the total points for each user with a simple query
 

SELECT u.username, SUM(pp.points) as total_points
FROM user u
    INNER JOIN user_team t USING (u_id)
    LEFT JOIN player_points pp USING (p_id)
    GROUP BY u.u_id
    ORDER BY total_points DESC

Edited August 31, 2013 by Barand

[dhirajkumar41]

 

Store the players' points for each player and not for each member of every user's team. That way you only have to add a record for each player getting points in a match rather than updating all the team records.

+-----------+                         +-----------+
|  user     |                         | player    |
+-----------+                         +-----------+
|  u_id     |--+                    +-| p_id      |---+
|  username |  |                    | | playername|   |
+-----------+  |                    | +-----------+   |
               |                    |                 |
               |  +-------------+   |                 |   +--------------+
               |  | user_team   |   |                 |   | player_points|
               |  +-------------+   |                 |   +--------------+
               +-<|   u_id      |   |                 +--<|  p_id        |
                  |   p_id      |>--+                     |  match_date  |
                  +-------------+                         |  points      |
                                                          +--------------+

Then you can the get the total points for each user with a simple query

 

SELECT u.username, SUM(pp.points) as total_points
FROM user u
    INNER JOIN user_team t USING (u_id)
    LEFT JOIN player_points pp USING (p_id)
    GROUP BY u.u_id
    ORDER BY total_points DESC

Thanks for reply. really Helpful.

 

Someone suggested me this solution.

 

 

user                              play

 

u_id(pri)-------- +            p_id(pri)---------------+      

username         |            playername               | 

                         |            POINTS                     |

                         |                                             |

user_team        |             player_for_teams     |

                         |                                             |

t_id(pri)    -------|----+            p_id (fk) ---------+

                         |     |

u_id (fk)    ------+    +---------t_id (fk)

 

 

And as you suggested, i added Points (in CAPS). so is this correct? 

Pls reply, thanks.

[Barand]

The player_points table in my version will enable you to find the winning user this week / this month / this season etc. If, as in the second version, you accumulate the points in the player record then you lose the time element and at some time you have the overhead of having to reset them all to zero (and lose any historical player performance data)

 

The second version's user_team table enables users to have more than one team if that is a requirement.

Edited September 2, 2013 by Barand

[dhirajkumar41]

The player_points table in my version will enable you to find the winning user this week / this month / this season etc. If, as in the second version, you accumulate the points in the player record then you lose the time element and at some time you have the overhead of having to reset them all to zero (and lose any historical player performance data)

 

The second version's user_team table enables users to have more than one team if that is a requirement.

Yeah.

everything looks in order now but only one problem.... Every team will have only 10  players. So, in the table "user_team"  you provided there will be only 10 rows for particular user, right? then where should i add team_name and rank.

If i add team_name in "user_team"  then it will be repeated 10 times just like u_id because of 10 players.is this feasible?

and about rank..i cant think of any solution to it? hope you will help there...

Thanks.

Edited September 2, 2013 by dhirajkumar41

[kicken]

The answer is another table.

 

You'd have user_team contain just one row with the team's info (team id, name, rank, user id, etc) then another table, say user_team_players which contains 10 rows for the different players, using team id rather than user id to link things.

[Barand]

You wouldn't store rank. That is derived data and you can find the rank with a query when required