2

I want to store a friend's list of every user in SQL server database. I am confused

  1. should I use table for every user, or
  2. should I just use one table for everyone, or
  3. Is there any other best way (I'm going to query it frequently)?

And how can I get all the friends of the single user when I query?

Pavan Varma
  • 1,199
  • 1
  • 9
  • 21
  • It's not a good practice to have a dedicated table for every user. Normally it will be a table for everyone (your option 2), and a relation table to specify the relationship between users. – Raptor Mar 08 '17 at 07:21

4 Answers4

3

When we say SQL Server then we are saying RDBMS.

Then there is normalization INF, 2NFand 3rdNF..., this is your question how to normalize a user and his friends data base.

My View, you need three tables

Individual (IndId (PK), Name ...)
User (UserId (PK), IndId (FK to Individual Table), login, password etc)
Friends(FID(PK), UserId (FK to User Table), IndId (FK to Individual Table))

Get all friends of a user

Select I.* from Friends F
     JOIN User U on F.UserId=U.UserId // Get all friends of all users
     JOIN Individual I on I.IndId =F.IndId // Get there names etc
   Where I.Name = 'MyFriend' // filter a friend
Community
  • 1
  • 1
Anil
  • 3,722
  • 2
  • 24
  • 49
2

what i did was created first table for list of all tbl_users . then created another table for tbl_friendslist. the function of tbl_friendslist table is to store the id of the users table ex. user 10 added user 11 the data will be stored in the tbl_friendslist table and i user ENUM to flag if the user 11 accepted,declined the request..

Lion Smith
  • 647
  • 3
  • 16
  • 48
1

You should have 2 tables:

TBL_Users will have a User_ID

TBL_Friends will have a Friend_ID and a Friend_User_ID

The correlation will be between TBL_Users.User_ID and TBL_Friends.Friend_User_ID

Koby Douek
  • 16,156
  • 19
  • 74
  • 103
1

You need two tables. User (id,UserName,...) UserFriendShip(Id,ToUserId,FromUserId,RequestStatus,RequestStatusReason)

Example: I want to be friend with you (We are users so user table has our records)

ToUserId : Your user Id FromUserId : My User Id ResquestStatus: Requested, Accepted, or else enum value so use tinyint not int for enum values in sql server. RequestStatusReason: This area is very important. Think that you first accepted and then rejected me. this area will store that detail.

Specify your necessities and then you can design it yourself I just tried to examine.

Dogan
  • 96
  • 6