0

Assumptions

  1. If A is a friend of B, B is also a friend of A.

I searched for this question and there are already lots of questions on Stack Overflow. But all of them suggest the same approach.

They are creating a table friend and have three columns from, to and status. This serves both purposes : who sent friend request as well as who are friends if status is accepted.

But this means if there are m users and each user has n friends, then I will have mn rows in the friends table.

What I was thinking is to store friends list in a text column. For every user I have a single row and a friends column which will have all accepted friends' IDs separated by a character, say | which I can explode to get all friends list. Similarly, I will have another column named pending requests. When a request is accepted, IDs will move from pending requests to friends column.

Now, this should significantly reduce the entries in the table and the search time.

The only overhead will be when I will have to delete a friend, I will have to retrieve the friend string, search the ID of the friend to be deleted, delete the ID and update the column. However, this is almost negligible if I assume a user cannot have more than 2000 friends.

I assume that I will definitely be forgetting some situations or this approach will have certain pitfalls. So please correct if so.

Community
  • 1
  • 1
Naveen
  • 7,944
  • 12
  • 78
  • 165
  • If you're expecting more than a few thousand users this approach is pretty much flawed performance wise in any case. I've benchmarked this before. Either use massive caching for the associations or go straight for a graph database to describe the associations, you can still use a SQL database for the data. For a longer explanation see my answer here http://stackoverflow.com/questions/1009025/facebook-database-design. – floriank May 04 '15 at 09:47

1 Answers1

2

The answer is NO! Do not try to implement this idea - its complete disaster.

I am going to describe more precise why:

  1. Relations. You are storing just keys separeted with |. What if you want to display list with names of friends? You will have to get list, explode it and make another n queries to DB. With relation table from | to | status you will be able to do that with one JOIN.
  2. Deletions. Just horrible.
  3. Inserts. For every insert you will need to do SELECT + UPDATE instead of INSERT.
  4. Types. You should keep items in DB as they are, so integers as integers. Converting ints into string and back could cause some errors, bugs etc.
  5. No ORM support. In future you will probably leave plain PHP for some framework. Take in mind that none of them will support your idea.

Search time? Please do some tests. Search with WHERE + PRIMARY KEY is very fast.

MacEncrypted
  • 184
  • 9