Friend system?

[Latox]

I have just coded my own basic "community" type of portal for one of my sites.

You can check my profile out here: http://www.youtubetomp3.net/profile/admin

I am currently in the process of coding a forum for it, after this I want to make a type of "friend-system".

I want it to work so say somebody goes to my profile (link above), there is a link which says Add as Friend, and it then adds me to their friends list.

I know how to do all of this, but I have never had any experience in storing mutiple values in one field, for example:

User ID 4 adds me (user id 1), someone else, (id 5) and someone else, (id 80).

I want to store that in 1 field, for example 1,5,80 - as it'd be more efficient, but I'm not sure the best way to do this, nor select the results as single values.

UserID : 4
Friends: 1,5,80

My user id field is called "id", thats all that should be needed really, haven't created the friend field yet.

Thanks in advance, very appreciated

If you don't understand something, let me know.

[guido2004]

I want to store that in 1 field, for example 1,5,80 - as it'd be more efficient,

No it wouldn't be more efficient. It would be a disaster Putting multiple values in one field is a clear sign of a non normalized database design.
Instead, create a friends table, which will contain one row for each user-friend combination. It'll be much easier to maintain and to query.

[Latox]

OK, I was thinking of doing it this way but one of my friends said it be "more memory efficient" to put it into one field.

I'll just create a friend table and have userid, friend and then select and count the data from this table where userid = profileid.

Can get the rest of the info I need with joins, thanks for clarifying.

Thanks

[oddz]

^

friends
-- user (users(id))
-- friend (users(id))

SELECT 
         t2.name AS friend_name
   FROM
           friends AS t1
  INNER
    JOIN
         users AS t2
       ON
         t1.friend = t2.id
 WHERE
         t1.USER = 4

All friends for a particular user

[Latox]

Yeah, I know how to do everything now, was just skeptical about the whole bunch of values in one field.

Thanks

[cranial-bore]

fewer rows != efficiency

As mentioned use one row per relationship. The combination of the userID, friendID columns should be the primary key. The query to select friends for a given user, or users who have friended a given user will be fast because of the index.

[Latox]

Just finished it, check my profile: http://www.youtubetomp3.net/profile/admin

Shows my friends on there.

Gotta do some layout changes and everything, will do that tomorrow, off to bed in a sec.

Let me know what you think so far.