Search code examples
mysqldatabasedatabase-schemadatabase-normalization

Normalization: is it done good?


I am creating game review database. I am new at this, but I am trying my best. Little bit about database: The system is simple, user fill out php form, where he is insert his name, email when choose feedback between is it good or bad and leave a comment/suggestion. I am adding photo how it looks visually, I am just sorry for external link, I can't upload here:Visual database

So here is my tables and the process how I went through normalization:

(I believe this is 1NF, correct me if I am wrong)

Table_review { 
    User ID 
    User name
    User email
    Feedback
    Comment
}    


(I believe this is 2NF, correct me if I am wrong)

Table_user { 
    User ID 
    User name
    User email
    Comment
}

Table_review { 
    Feedback ID 
    Feedback
}

(I believe this is 3NF, correct me if I am wrong)

Table_user { 
    User ID 
    User name
    User email
    Comment
}

Table_review { 
    Feedback ID 
    Feedback
}

Table_user { 
    User ID 
    User name
    User email
    Comment
}

Table_whole { 
    User ID 
    Feedback ID
}

My questions:

Is it normalized?

Should "Table_whole" include its own ID?

I am saying thank you for any help, because I am really running out of time..


Solution

  • -- The user can make as many comments as they like
    Table_review { 
        User ID 
        Game ID
        Feedback ID
    }    
    
    Table_feedback {
        Feedback ID
        Comments
        TimeStamp
    }
    
    -- Now users can comment on what other users think 
    -- but only to one level of nesting
    Table_feedback_comments {
        Feedback ID
        User ID
        Comment
        TimeStamp
    }
    
    -- a user can only rate a game once, but they can change their rating over time
    -- For example more content was added and the user likes this 
    -- and wants to reflect that in their rating
    Table_rating {
        Game ID
        User ID
        Rating
    }
    
    Table_user { 
        User ID 
        User name
        User email
        Comment -- something that describes the user
    }
    
    -- Even if you have only one game you can still store it in the database
    Table_game {
        Game ID
        Game Name
        -- other columns used to represent a game
    }