3

I am working on the data model for a relational database where I have to store User Information as well as User's profile such as Education Level, personal interests, hobbies, and etc. Similar to what most of the social networking sites have or any other systems that allow you to build a profile.

I cannot decide if it would be better to store all this information in one Users table, or break it into 2 tables.

If I would break it into two tables I would have Users table that would just store UserID, Name, e-mail, DOB, gender.

UserProfiles would store the rest of the stuff pertaining to a profile, sharing the same UserID with Users table

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
ssokol91
  • 552
  • 3
  • 13
  • 25

3 Answers3

3

  1. If there are multiple profiles of a single user means one to many relation then i would recommend you to create 2 tables one is user and other is user-profile.

  2. If one user have only one profile then your should create only one table with both attributes on User as well as profile.

Mujassir Nasir
  • 1,640
  • 4
  • 31
  • 52
  • There is only one profile for a single user. My main concern was the response time for searching. Lets say I have text fields such as "Favorite Music" or "Favorite Books". If a user wants to search to find a match on those fields, would it be better to store these items in a separate tables (UserID, FavoriteMusic [varchar(2000)]) for example? – ssokol91 Jan 04 '12 at 06:24
  • I believe this is the best answer in terms of simplicity. – hussam Feb 25 '22 at 03:34
3

Go for the more modular design, this will allow for more flexibility and control. The only time I would recommend keeping the data in a single table is if you plan to query the same data frequently.

There is a great article here which goes into depth as to why joins are expensive. You should ultimately base your decision off the information provided in the link, however as I mentioned before if you plan to query the two tables together frequently then keep the data in a single table.

Community
  • 1
  • 1
Dennis
  • 3,962
  • 7
  • 26
  • 44
2

I think,in order to decide which data model to chose, just look at some of the similar requirement Datamodels, which are presented in this Datamodel Library.

Specific to your User-profiles Datamodel Requirement, this link may be useful.

Hope those links will be useful. OR I got this Data model image depicting facebook type data model:

enter image description here

parmanand
  • 468
  • 2
  • 7
  • 18
  • Links to external resources are always good, but can you summarize the content of your answer? Sometimes external links go away or change, and then your answer would no longer be helpful. – Jonathan Hall Jan 03 '12 at 06:15