1

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.

  • How you will store information there, will there be multiple entry in table User_Personal_details, User_Profile, User_Statistics for a single user, How you are storing address info there – vijayp Oct 29 '14 at 9:13
  • @vijayp No, all tables are for single users and the user_id will be foreign key from User_Account_details to each other table. – Fenil Oct 29 '14 at 9:18
  • I See all those four tables can be changed to two table, one for saving all the login related info and other for saving aothe user info – vijayp Oct 29 '14 at 9:51
  • @vijayp Can you answer my question? i'll highly appreciate. – Fenil Oct 29 '14 at 9:53
  • what are country and city in User_Personal_details Id from country_city_association? – vijayp Oct 29 '14 at 10:18
1

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
  • But, will it not break the rules of normalization? – Fenil Oct 29 '14 at 9:53
  • @xyz which rule would it break? – Dan F Oct 29 '14 at 10:05
1

For user details, I would suggest to have two tables and one for saving User Address

 User_Account_Details(user_id, primary_email, secondary_email, password)

 User_Personal_Details(user_id, first_name, last_name, profile_picture,
                       display_name)

 User_Address(user_id,country, city)

 User_Statistics(user_id, trips_count, likes_count, reviews_count)

Now, you can go for adding more columns in User_Address table for saving more information in address.

  • thanks..But the statistics are to be counted by stored procedure and they are to be counted from other tables. Can't I make the separate table for it? – Fenil Oct 29 '14 at 10:28
  • statistics will change frequently right? and do you need to save save other info also for satictics like tripsId, reviewId etc? – vijayp Oct 29 '14 at 10:34
  • Ya...statistics will be changed frequently. – Fenil Oct 29 '14 at 10:35
  • Yes, you can go for adding one User_Statistics tables(edited my answer), as these table will keep a different set of info in a seperate table. The rest design looks good to me – vijayp Oct 29 '14 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.