help needed with design db

Hi,

with a few friends i play some boardgames. Suddenly we felt the need for some statistics, so i thought i'd do the job. But i don't know how to make 1 table.
i have right now

1. game_id
2. game_name

1. player_id
2. player_name
3. player_email

but what should the third table look like
is this the proper way

1. played_game_id
2. game_id
3. player_id
4. score
5. date

or should it be like this

1. game_id
2. player_id
3. score
4. player_id
5. score AND SO ON
6. date

I hope my problem is clear. with all those lists. It's hard to explain it in words, so i hope the reallife example will speak for itself.

thanks for any help,

Sarnix

if i'm correct the combination of game_id and player_id gives you a unique key to identify the third table, so to my oppinion

1 player_id
2 game_id
3 score
4 date
5 etc..

would be sufficient.

You could give the table a unique key for itself, but that's not required.

1 played_game_id
2 player_id
3 game_id
4 score
5 date
6 etc..

I prefer the first option. Put the player_id before the game_id in the table, since there are fewer players than games ( i hope)


Also you might want to reconsider using the underscore "_" in table names

what's wrong with "playerId" ?

thanks for your answer Targaz

i see,

but would you put multiple players + their scores in 1 record or make a seperate record for each player with his/her score?

i kinda got used to the underscore. is there a serious reason to reconsider?

( sidenote: so far there are more players than games ( kolonisten(dutch), civilization and la citta))

Ah now I see, a game is not a match but a (board)game.
Silly me (game/set/match)

That could make things a bit more difficult (read interesting).

One entry per game per player per match (date?).

Something like this ?

game
game_id
game_name

player
player_id
player_name

match
match_id
match_date
game_id // one boardgame - one match

playedgames
match_id
player_id
score
- one record for each player that playes the match

Don't pin me down on this, I made it up in five minutes. Maybe you could read some books or search the web on database-design. Comment anybody?

I'm not sure every databasesystem supports underscores that happily. Again comment anybody?

Always happy to help someone who playes Kolonisten van Catan.

Ah yes, that makes sense.
I thought of 3 tables, but 4 tables and the way you describe them seems very suitable for my situation.
thanks, Targaz.

Note taken about the underscore. mysql has no problems with them, so it'll be allright for now.

hehe, you might want to try La Citta if you're looking for a new game to play.

No redundant information...
Looks fine to me Hehe, indeed! It's a great game IMO. I was wondering though if it existed in the USA or Australia as well since I have some relatives who never heard of it before (original name would be Die Siedler von Catan (german) I think)

I don't think it exists outside europe. Did anyone ever play the computerversion ? That is some sad software. The game is somewhat altered, but they translated from German to Dutch by some who has obviously never done that before.

Above that the IA-players are the most anyoing thing I have ever seen in my life in a computergame.

Since when do people in Enschede have acces to the internet?

Bah, then I'll have to export it Yup, I played it and found it not that bad though, although I must admit the Dutch isn't very great and the AI-players are a pain in the *ss (that's why I turn their speach of the moment I start ) a game... still prefer the boardgame though since it's much nicer to have some heated discussions with friends when they block my streets or resources etc.. Ohh, not that long, I switched from smoke signals as my main tool of communication to the internet only last month

Sigh... people from the randstad