Friends/buddy system database structure

Databases
1

Hi, I’m about to integrate a friends system into my website and I was wondering what the best schema would be for this kind of thing.

The friends system will be an invite only system, so a user must send an invite to the user he wishes to add to his friends list before that friend can be added. The invites will be stored in an invites table, but I was wondering on how best to record the friendship between two users (once an invite is accepted).

I was going to follow vbulletin’s schema of which stores this information in a single field, in the members table. This field consists of the user id of each friend, seperated by a space.

My second option is to set up a seperate table which stores every single friendship. Although this would be easier to manage/handle (since each friendship has to be mutual), I’m a bit worried about the excessive amount of records this would create.

Would anyone know what the best approach would be? I’d be very grateful for your input.

Thanks

2

A special table for the data takes advantage of the database capabilities. In general tables are designed to hold loads of rows and data.

The biggest issue you have with the vbulletin approach is that you have to cap the number of friends per user. For example a space delimited field might be a varchar(255). Lets say on average a user id is 4 digits long. Including the space, that’s 5 of your characters gone per friend. Thats a max 51 friends per user. You also cant create table joins based on a space delimited list either which further restricts its usefulness.

That said, MySQL has a SET data type which kind of gets around this, but its only a max 64 item field.

Using text fields will only lead to poor performance when you try and query the data.

I guess you really need to take a guess at the number of users you are expecting in the site before you can work out what criteria you need to apply.

Personally I would go with the separate table approach, then consider something like table clustering in the event that the table becomes overloaded and too resource heavy on the system.

3

Actually just thought of a slightly different approach. Why not store the referer user id against each member? Every member will only have one existing user that referred them anyway, so all it would take is a single integer field against each user record. Solves the data overload problem.

It’s not so much a list of friends as a list of people you have invited into the system that were accepted.

Is your friends system going to allow users to link other existing users as well as their friends or only record the ones they invite?

4

Thanks for your reply mrsmiley. To clarify, this friends system isn’t a referer system - it’s a friends system of which requires a mutual arrangement between two members to become friends. This is where the invites come in - one member can invite another to be their friend, if that person accepts, they become mutual friends and this will be displayed on their public profiles.

I guess you really need to take a guess at the number of users you are expecting

It’s difficult to estimate because I’m on two months in, but I do expect to attract a lot of users. I’m looking to attract at least 50-100,000 members in the next 12 months - whether they’ll be active or not (with regard to using the friends system) remains to be seen.

Using text fields will only lead to poor performance when you try and query the data.

The data would be pulled out on a members profile page and in the friends management feature. vb’s method is basically a mediumtext field, but placed in a special usertextfield table rather than within the users table, so I guess that doesn’t affect performance as much when quering main user data. I wonder whether it’s worth creating a seperate textfield table in this way!?

The only problem with the above is that I would then have to synchronise vbulletin’s user table with yet another custom table (I already have a synchronised custom members table). But I’m thinking this would eliminate having to insert a record in a friendship table for every mutual friendship. Hmm… :confused:

Any further input/suggestion would be appreciated. Thanks for your help :slight_smile:

5

here is (a stripped down version of) my friends table structure:


create table g_friends (f1_id varchar(35), f2_id varchar(35), status varchar(3), PRIMARY KEY(f1_id, f2_id));

the “status” field is used to indicate what stage it is in (pending, active, etc).

when Bob requests a friendship from Amy, a row is created with Bob’s user id as f1_id and Amy’s user id f2_id. the status is set to pending.

to see who sent amy a friend request, we simply search the table for f2_id = Amy’s user id.

to confirm, we simply change the status to ‘active’

bada-boom!

(this assumes the id fields are foreign keys. to grab user names and crap, use “inner join” to your main user table that stores that stuff)

6

any time you store more than one occurrence of something in a single column value (no matter whether they are space-delimited, comma-delimited, whatever), you violate a fundamental database design concept called first normal form

i advise you not to do that without adequate experience of the costs involved, and what you’re giving up for it

7

you violate a fundamental database design concept called first normal form

I can cope with that :slight_smile:

Thanks r937 (and tuxidomasx, for your example) - your help is truly appreciated.

8

Have I missed anything, in terms of key usage, in my schema below?
And as I understand, MyISAM doesn’t support foreign keys, correct?

CREATE TABLE friends (
userid_1 varchar(8) NOT NULL default ‘’,
userid_2 varchar(8) NOT NULL default ‘’,
timestamp int(10) unsigned NOT NULL default ‘0’,
status tinyint(1) NOT NULL default ‘0’,
PRIMARY KEY (userid_1,userid_2),
KEY status (status)
)

9

the DEFAULTs are wrong, but maybe that’s not your doing

the index on status may never get used, as you are not likely to want to access this table by status only

you might consider adding an index on (userid_2,userid_1)

you are correct, myisam does not support FKs

10

Thanks for clearing that up. Unfortunately, I’m struggling with the query. I’ve never worked with inner join (only left join) and I don’t know how to retrieve a list of friendships from a certain userid.

If I wanted to find a certain persons friends via their userid, I need to look in both userid_1 and userid_2, and then retrieve (join) the usernames from a seperate table. Could you please assist with the required query?

Again, thank you!

11 
select users.userid
     , users.username
  from (
       select userid_2 as friend
         from friends
        where userid_1 = 937
       union
       select userid_1 as friend
         from friends
        where userid_2 = 937
       ) as friends
inner
  join users
    on users.userid = friends.friend
12

I wasn’t even close :smiley:

Once again, thank you - that’s absolutely excellent!