Saving Database Standards

Question

I'm working on an Android app where people can register and login with their account. This information will be saved to some sort of database. So there will be a table, where each row is a new user. The table will have 3 columns for each user: username, password (encrypted of course), and friends. So yes, the users can friend other users.

The question I had is, what is the industry standard in implementing a table like this with the friends column.

I was thinking since each user will have an unique ID, the friends column for each user can just be a comma-separated list of the user IDs. Something like this:

ID    Username    Password    Friends
1       blah        ****        2,3
2       blah1       ****        1
3       blah2       ****        1

But the way I'm thinking of sounds super sketchy....how does someone like facebook setup their database to store friends. Or if facebook is too complicated...how is the best way to setup my database to store friends?

You could clarify your question by editing a descriptive title, omitting the irrelevant details about passwords, and avoid the terms "industry standard" ([technical standards](https://en.wikipedia.org/wiki/Technical_standard) are formal/official documents, whereas you seem to be asking about common practices). — Basil Bourque, Oct 24 '13 at 04:05
Possibly a duplicate of question [How to make SQL many-to-many same-type relationship table](http://stackoverflow.com/questions/18603372/how-to-make-sql-many-to-many-same-type-relationship-table) — Basil Bourque, Oct 24 '13 at 04:14

score 1 · Answer 1 · answered Oct 24 '13 at 04:11

In the first place i would not store the friends column inside the table the way you have stored. Many to many relationships are usually stored as below into a new table altogether.

 ID               FRIENDS

 1                  2 

 1                  3 

 2                  1

 3                  1

The primary key for the table is (ID, FRIENDS) i,e, one to one mapping rather than one to many.

score 0 · Answer 2 · answered Oct 24 '13 at 04:22

I would use a table called friends with a many to many relationship, so in your example

        USER TABLE                         FRIEND TABLE

    ID    Username    Password              ID1    ID2
    1       blah        ****                 1      2
    2       blah1       ****                 1      3
    3       blah2       ****

To be more efficent add index in both direction (ID1,ID2) and (ID2,ID1), so you can add a friendship only one time and you don't need to add (1,2) and (2,1)

Saving Database Standards

2 Answers2