I need help with normalising the data derived from a survey

Question

Background information: I have been assigned to make a survey on E-sports and build a database. My survey can be accessible here https://devpatel6.typeform.com/to/MB1DYP . In brief, this survey contains few question on what players prefer using and what they play. I have made the question but I need help with normalising or categorising the data for the ER diagrams. Here is what I have done so far:

User

• Firstname
• Lastname
• Email
• Address
• Age_Category
• User_ID

Game 1

• Game1_Name
• Time_Spent_by_user_on_game1
• Personal_detail_collectionORnot
• Requires_internetConnection
• MultiplayerOrSingle
• Include_live_features_or_not
• Operating_systems
• Game1_response_ID

Game 2

• Game2_Name
• Time_Spent_by_user_on_game2
• Personal_detail_collectionORnot
• Requires_internetConnection
• MultiplayerOrSingle
• Include_live_features_or_not
• Operating_systems
• Game2_response_ID

Game 3

• Game3_Name
• Time_Spent_by_user_on_game3
• Personal_detail_collectionORnot
• Requires_internetConnection
• MultiplayerOrSingle
• Include_live_features_or_not
• Operating_systems
• Game3_response_ID

EsportResponse

• HeardOfEsports
• Prefer_watching_esports
• EsportsClub_name
• OpinionScaleOfEnjoyment
• User’s_timeSpent_watching_Esport
• Log_in_through_social_media_yesOrNo
• Types_Of_game_user_plays
• Visits_esport_events_Y/N
• Money_spent
• Using_Headphones_Y/n
• TrackOfPersonalAcheveiment
• Build_careerOn_Esport

Can someone help me categorise this data in further tables. I am supposed to be drawing a Conceptual ER Model after my normalization. I just can't sort that part out.

Answer 1

I would probably start out with a Respondents table (one row for each person who responded to your survey), a Games table (one row for each game in the survey), and a bridge table, Respondents_Games to connect the two (RespondentID, GameID).

For multiplatform games, you would probably place attributes like operating system and other properties that may vary by platform in this bridge table.

Once you've got that, try cleaning up and normalizing the Games table, so that you don't have duplicates - like if multiple users play Counterstrike, you'd want only a single row called Counterstrike instead of one row for each response.

I would often recommend placing the yes/no type questions as columns on the Respondents table, but you could create a table called Questions and one called Responses that connects respondents (or respondents and games) to questions with their respective responses.

Try to strike a balance where the degree of normalization is "good enough" for your purpose. Where exactly this balance is depends on your output/reporting needs.