Jump to content


Photo

Help with database design - complex relationship

mysql php database design relationship

  • Please log in to reply
9 replies to this topic

#1 dhirajkumar41

dhirajkumar41

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 26 August 2013 - 09:28 AM

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

 

 



#2 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,667 posts
  • LocationBonita, FL

Posted 26 August 2013 - 02:46 PM

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.
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#3 dhirajkumar41

dhirajkumar41

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 27 August 2013 - 10:46 PM

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 



#4 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,667 posts
  • LocationBonita, FL

Posted 27 August 2013 - 11:50 PM

Yes, for each user, within user_team you'd have 10 rows where u_id would be the same but p_id would change.
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,171 posts
  • LocationCheshire, UK

Posted 31 August 2013 - 05:23 AM

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, 31 August 2013 - 05:37 AM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 dhirajkumar41

dhirajkumar41

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 01 September 2013 - 11:23 PM

 

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.



#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,171 posts
  • LocationCheshire, UK

Posted 02 September 2013 - 01:00 AM

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, 02 September 2013 - 01:03 AM.

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#8 dhirajkumar41

dhirajkumar41

    Newbie

  • New Members
  • Pip
  • 6 posts

Posted 02 September 2013 - 01:25 AM

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, 02 September 2013 - 01:33 AM.


#9 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,667 posts
  • LocationBonita, FL

Posted 02 September 2013 - 01:48 AM

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.
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#10 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,171 posts
  • LocationCheshire, UK

Posted 02 September 2013 - 02:01 AM

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


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com