Database skills rusty does this schema look correct?

[otemu]

Hi,

Trying to create a ranking system, however my database skills seem to be rusty does the schema look correct?

Overview.

Members join
Members can challenge other members
Challenges must be accepted by opponent
Members can Play several games
Scores for each match
Overall wins and loss for each member

Not quite sure about the opponent table but was added as members ID cant reference the two fields (ChallengerID, OpponentID) in the challengers table.

 

[fez]

Not sure that is the best schema for what you are trying to do. Maybe the table names are throwing me a little. You want to have a table:

To store a players detail
To store a game information

Thats about it isnt it?

The players details table will store their name, rank.

The games table will say whether the game is finished, ongoing or pending acceptance from opponent. It will store the ID's of the two player and the result of the match.

You can then find out the number of wins and loses by joining the two tables on the id of the player.

If there is anything that you think wont be possible with just the two tables let me know and ill have another look.

 

[otemu]

Hi,

Thank you for your reply. Sorry was a quick mock up I did to try give people an idea, probably not the best naming convention or explanation, please excuse my long reply. Basically am building a clan site and wanted to implement a ranking system.
The idea was for users to be able to register to site, and register a number of games to their profile, Street Fighter 4, Warcraft 3, CSS etc.

Once registered there be able to challenge other members to such games, on clicking a member of the site there be able to issue a challenge request, if accepted then a schedule date and time for the match will be set.

After scheduled time the users involved in the match will be able to click if they have won or loss, this will then update the stats on their personal records for that particular game and for their overall rank.

Idea behind my schema was as follows:

Tbl members – stores there information, name, age, location etc
tbl game – stores all the games
tbl challengers - stores all challenges made, status of challenge pending, accepted declined, results of match
tbl profile – stores all the different games each member plays also includes overall rank and win rate
tbl opponent – added to force referential integrity

opponentID and challengersID are foreign keys for the primary key memberID
It’s the opponent table that makes me feel like something wrong this was purely created as if I reference both the memberID in one table I can’t force referential integrity, which means I only have one proper relation to one memberID and not both.

I don’t think two tables work as a member can play many games, also can’t run queries if both id of the player existed in one table as it find relationship of one but not the other, or am I totally wrong…….

 

[suarve]

Do people have to be a member to challenge other members?

 

[otemu]

Yes