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:
User can register by providing primary email and password.
Registered users can add their personal details like they can add full name, secondary email, country and city.
User can setup their profile like they can add profile picture, Display name, places they have visited etc.
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.