Jump to content

Help with database design - complex relationship


dhirajkumar41

Recommended Posts

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

 

 

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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 by dhirajkumar41
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.