Mysql – Need help about normalizing database to 3NF

database-designMySQLnormalization

I am currently working on a city travel guide application which let users explore places and plan trip.

In the account module, the following functions are to be considered:

  1. User can register by providing primary email and password.

  2. Registered users can add their personal details like they can add full name, secondary email, country and city.

  3. User can setup their profile like they can add profile picture, Display name, places they have visited etc.

  4. When they open own/ other's profile, their profile info (like photo and Display name) and statistics like their 'trip' count, 'likes' count, 'reviews' count are to be shown.

I have designed database tables for account module as follows:

 1. User_Account_details(user_id, primary_email, secondary_email, password)
 2. User_Personal_details(user_id, first_name, last_name, country, city)
 3. User_Profile(user_id, profile_picture, display_name)
 4. User_Statistics(user_id, trips_count, likes_count, reviews_count)

There are 3 more tables for cities and countries so that when user add country , then the app retrieve it from database and according cities are displayed in suggestions for city textbox.

 1. country(country_id, country_name)
 2. city(city_id, city_name)
 3. country_city_association(country_id, city_id)

Can anyone tell me the database tables are properly normalized to 3NF or not? And if yes, one more confusion: Should I normalize tables upto 3NF or BCNF? sorry, I am a newbie in database-design. plz help.

Best Answer

You don't need four tables for the user account data. Each user only has one primary email, one secondary email, one first name, one profile picture, one count of trips etc. If there's only one of each entry per user, why not put them all in the same table?

You can set a lot of the fields to be nullable so that they don't all have to be filled in, and you might also want a "registration status" field to show if they're a guest or a registered user.

If you only want to show users certain parts of another user's profile, you don't need to store the publicly available information in a separate table, you can use a query like this:

select profile_picture, display_name, trips_count, likes_count, reviews_count
from user_table where user_id = @SelectedUser
Related Question