For any Facebook Application, I must store a listing of buddies of the user during my MySQL database. This list is asked for from the db, in comparison along with other data, etc.

Presently, I store this listing of buddies inside my user table, the uids from the buddies constructed in a single 'text' area, having a '' as separator. For instance:

ID - UID - Title - Buddies => 1 - 123456789 - Someone In Particular - 987654321123456765432

My PHP file demands this row and extracts their email list of buddies by overflowing that area (''). All of this works fine, every 1000 customers are about 5MB diskspace.

The problem:

To have an extra feature, I should also save what they are called from the buddies from the user. I'm able to do that diversely:

1) Save this data within an extra table. For instance:

ID - UID - Title => 1 - 1234321 - Jane Doe

Basically require the title from the friend with ID 1234321, I'm able to request the title out of this table. However, however , this table could keep growing, until all customers on Facebook are indexed (>500million rows). My webhost won't such as this! This type of table will require about 25GB of diskspace.

2) Another option would be to increase the information held in the consumer table, with the addition of the title towards the UID within the buddies area (by having an extra separator, let us use ','). For instance:

ID - UID - Title - Buddies => 1 - 123456789 - Someone In Particular - 987654321,Mike Johnson123456,Tom Vibrant765432,Ron Cruz

With this solution I must affect the script, to include another extra explode (','), etc. I am unsure the number of extra diskspace this normally takes... However the data does not get simple to handle by doing this!

3) Another solution provides a good overview of all of the data, and can make the database to become huge. Within this solution we produce a table of buddies, having a row for each friendship. For instance:

ID - UID - FRIENDUID => 1 - 123456789 - 54321

ID - UID - FRIENDUID => 3 - 123456789 - 65432

ID - UID - FRIENDUID => 2 - 987654321 - 54321

ID - UID - FRIENDUID => 4 - 987654321 - 65432

As you can tell within this example, it gives an excellent overview of all of the relationships. However, about 500million customers, and let us say typically 300 relationships per user, this can produce a table with 150billion rows. My host is certainly not likely to like this... And i believe this type of table will require lots of diskspace...

So... How you can solve this issue? What is your opinion, what's the easiest method to keep UIDs + names of buddies of the user on Facebook? How you can scale this type of data? Or have you got another (better) solution compared to three options pointed out above?

We do hope you might help me!

Basically require the title from the friend with ID 1234321, I'm able to request the title out of this table. However, the issue is this fact table could keep growing, until all customers on Facebook are indexed (>500million rows). My webhost won't such as this! This type of table will require about 25GB of diskspace.

If storing what they are called from the customers you'll need really takes 25GB, it takes 25GB. You cannot move data around and expect it to obtain more compact - and also the overhead of the table isn't that much. Rather, you have to concentrate on only storing the information you really need. It's unlikely that everybody on Facebook uses the application (whether it were the situation, you should not be utilising a number where 25GB of space is really a worry).

So rather than indexing the whole of Facebook (which may be a challenge regardless), just keep data relevant for anyone who really make use of your application as well as their immediate buddies, the industry much more compact dataset.

The first suggested option would be the best way to get it done it removes any potential redundancy in title storage.

To be sure with Amber, solution 1 will probably be the best method to store this data. If you wish to stick to your present approach (much like solution 2), you might want to consider storing the friendship data like a JSON string. It will not make the least possible string, but it will likely be super easy to parse.

In order to save the information:

$friends = array(
    'uid1' => 'John Smith',
    'uid2' => 'Jane Doe'
);

$str = json_encode($friends);

// save $str to the database in the "friends" column

To obtain the data back:

// get $str from the database

$friends = json_decode($str, TRUE);

var_dump($friends);