SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    always learning . . .
    Join Date
    Nov 2003
    Location
    UK
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is this table normalized

    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

  2. #2
    Non-Member
    Join Date
    Aug 2002
    Location
    USA
    Posts
    544
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    always learning . . .
    Join Date
    Nov 2003
    Location
    UK
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    always learning . . .
    Join Date
    Nov 2003
    Location
    UK
    Posts
    821
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    #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.

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •