Is this table normalized

[Tezler]

Please help if possible as.

Before I had table with columns as below

player_id
opponnent_id
frame_1_f
frame_1_o
frame_2_f
frame_2_o
highbreak

It is for a game of snooker. In the table above 4 rows of data will hold details for 1 match as the player_id cycles from 1 > 4 so player 1 will have 4 scores of which 2 are opponents for frame 1 and 2 and 2 scores are his in the 4 frame fields.

Simple but I were instructed to normalize the table. Often means split into seperate tables. with key links to link them. I found this so hard as 4 rows meant 1 match.

So I came with up with this.

please advise:

#table opponent
opponent_id (key)
opponent_title
opponent_location

#table player
player_id (key)
player_firstname
player_lastname
player_nickname

#table match
match_id (key)
opponent_id (link)
player_id (link)
match_frame_1_f
match_frame_1_o
match_frame_2_f
match_frame_2_o
match_highbreak

again this way still 4 rows of data in table match make up 1 match as again it cycles through 1 > 4 of player_id in player_table. This is the link form table match to table player and then same with the opponent_table (opponent are teams not players like us 4).

Im not too sure i got the hang of this full thing with the linking, and key fields. What is what ? why is what ? what mess have i made ? and so on ? I will be so so happy if any one can help especially in the very very near future. Some ones checking it tomo.

Thanks a lot. Appreciate you guys so much

[pas]

This should be helpful:
http://www.databasejournal.com/sqlet...le.php/1428511

[Tezler]

I have read so much including nearly a massive chapter on NORMALIZATION. By all means i'll start reading but from reading is what I came up with above. Really need a hand to understand, follow and sort the tables. Maybe I have done a pretty OK job I just dont know.

[Tezler]

After reading that it seems I think the structure I have is infact ok. But is it, any one ? im sure it follows the 3rd form.

[Dr John]

#table player
player_id (key)
player_firstname
player_lastname
player_nickname

#match
matchID (key
player_1 (fk, player_ID)
player_2 (fk, player_ID)
frame_1_P1
frame_1_P2
frame_2_P1
Frame_2_P2

Or however many frames youthink you need.

There was another thread on exactly the same topic. basically youare making player and opponent different entities, but they are not, they are both just a player.

[Dr John]

Better still, and normallised as fully as I think is possible,

#table player
player_id (key)
player_firstname
player_lastname
player_nickname

#match
matchID (key)
player_1 (fk, player_ID, from player)
player_2 (fk, player_ID, from player, except it is of course a different one, and you can set a rule to exclude someone playing with themself)

with the final table
#frames
frameID (PK)
match_ID (fk, from match)
frame_P1
frame_P2
highbreak

This enables you to have as many frames as are required. eg best of five needs three or four or five frames (3-0, 3-1, 3-2) but storing five frame results in match gives lots of nulls, while this method only uses as many frames as are played. And you can increase the number played in the "final" with no effect on the tables.

player---<match----<frames