0

I was wondering what was the best way to generate a followers list using PHP and MySQL database. So here are the two ways I have found that this can work.

Here is the first way that I have found: I can create one column for each user that will contain all of the ids of the users that they follow. When I go to retrieve the list, I explode the list of ids so that I can select all of the data from each user.

Here is what it looks like in the database: 

User ID              Following
1                    2,3,5,6,7,9,10
5                    10,5,2,20,1

The other solution that I have found is that every time a user wants to add someone to their "following" list, the database will contain 1 row just for that specific person that they are following.

User ID        Following
1              2
1              5
2              1
1              42

Therefore, in the long run, I am wondering which would be the most effective way to organizing the data and retrieving data to display for the newsfeed - the easiest and least cost effective. (I already have a table that contains all of the users posts).

halfer
  • 19,824
  • 17
  • 99
  • 186
mwittner
  • 9
  • 1
  • 6
    Don't go with solution 1 (exploding the values) - ***normalise your data*** – ʰᵈˑ Apr 17 '15 at 16:16
  • Please read this post entitled "Is storing a delimited list in a databse column really that bad?" https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Andy Lester Apr 17 '15 at 16:34

2 Answers2

3

Use the second option: a table with a row for each following record. Databases are designed to handle millions of rows so don't be scared of using them. In particular, rows storing just integers will be very efficient. You can freely use joins and other features.

The comma separated string solution will not scale up because it's very inefficient for the database engine to have to parse that out constantly, especially if it grows considerably. Also consider what field length you'd put on that, you might find you have to regularly resize it as users gain more followers.

MrCode
  • 63,975
  • 10
  • 90
  • 112
0

The two solutions are of two types for retrieval and manipulation of the data. This will be a call for taste and strengths.

DATABASE/JSON/CODE The first will be a code solution to manipulate the data after retrieval of the string of followers. The cons to this are the typical storage of information as a delimited string is the bane of database admins and programmers. If you to add meta info to the follow connection you can't. Adding e.g. "followed since", "tag", "followers in group" becomes impossible without doing some very nasty hacks that will add unwanted code to your app.

You can fix this by storing the followers as a json object or array. This will make your code better and ease the manipulation. It will allow for adding meta data to the follow. Once you've done that the pro is polymorphism of a function to do things is easier after a single database call SQL in every instance. But the code can become complex after a bit, an d scalability might be a problem. Think about user that have thousands of followers.

DATABASE/JOIN TABLE/ MVC MODEL The second is a SQL solution is called a join table this has minimal cons like making a database call for each manipulation slows things down. You can fix things a bit if you are using MVC. I recommend making a model for polymorphism.

The pro is you can add other information to this table like the date the follow started or ended. This makes your app more comprehensive. The SQL becomes more complex but the coding is easier.

So all things adjusted and equal(after changing to JSON) you only have to choose between being a coder or SQL'er. Don't let a thing like scalability or performance influence your decision until they are actualities. But storing CSV in the database is a thing you should NOT do.

Carl McDade
  • 634
  • 9
  • 14