If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Is this correct database schema?

I am using phpmyadmin.
I want to create a website (like fantasy football) in which users will create account and select the players from the pool to create their team of 10 players.
Based on performance each player will earn points. and then user will also earn points from 10 players(his team).

so i searched on internet and came out with db schema.

1)User (u_id, details)

2)Players(P_id,details,points)

3)user_team(t_id, u_id(FK), p_id(FK), team_name, points)

so now problem is, there must be only 10 rows in the user_team table for a particular user, correct?

I am confused about it? Is this the correct schema? or am i missing something..something more accurate?
Pls help.
Thanks.

I'd probably enforce the team size restriction in the application code, for two reasons: (1) you can then easily change that restriction by changing a single configuration parameter in your code, and (2) I can't think of any way I'd want to do it via the DB.

PS: I have some vague thoughts about the overall schema, but no time to explore it at the moment. Maybe I'll get back to you later today or this weekend?

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

As far as enforcing the 10 player per team limit, the two options I'd look at are to either simply enforce it in the application code (select count of players by team ID before allowing an insert to be done), or you could create a stored procedure for the insert that would return false if the team already had 10 players, else go ahead and do the insert. The latter would be more efficient and there fore faster, but the former may well be plenty fast enough if you index everything correctly and aren't getting Facebook type traffic to your site.

"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation

Thanks, NogDog.
I think schema you provided is a simpler version.
In my earlier schema, user_team table might have more repeated attribute values for like rank, team_points and etc.
In this schema, 'team' table is very simple to manage (Every user_id will have only one team_id).

But in the player_to_team table, team_id still have to be repeated 10 time to include player.
But i dont think there's other option,is there?

You also mentioned indexes. To be honest, i dont really know anything about indexes. But i will find a source to learn what it is
and how it will help me here. Thanks again.
and let me know if you find more efficient solution.