table for 'friends', is this the best way?

Ok so I am making a table for friends on my site. here is what I have thought up:
the table looks like this:
CREATE TABLE friends (
id int(14) NOT NULL auto_increment,
userid int(14) NOT NULL default '0',
friendid int(14) NOT NULL default '0',
friendname varchar(20) NOT NULL default '',
pending varchar(14) NOT NULL default 'yes',
askee varchar(14) NOT NULL default 'yes',
PRIMARY KEY (id)
) TYPE=MyISAM;
so it has an id, userid, friendid, friendname, penidng, and askee row.

now what im doing is this:
the user requests friendship, this puts the userid in the friendid and reqested friends id in the userid. The askee is labeled yes. Then there is a second query that does the opposite, so that the friend asked has the other persons info. That's sort of confusing.. but it works.
Now I am worried that this will get REALLY big REALLY fast. What would be best? Should I simply put the initial persons userid in once, and then for the friendids/friendnames maybe just update, put commas between, and then seperate it out when I retrieve the info? Am I on the right track with what I have?
hope it made sense, thanks.

thecharking,

You have me totally confused! What exactly are you trying to do?

My only concern with using comman seperated values is that the reason you're using a DB is so that you don't seperate values by commas. Instead, you have multiple rows, each with a single record.

If you can explain what it is you're trying to do, it may make it easier to help you out.

sorry I was worried that it was abit confusing! I am making a site which will let users add other users as friends. I want it to be a requesting process, where one person, lets call him noah, requests another person (laura) to be friends... here I will dump the data for the friends table...

CREATE TABLE `friends` (
`id` int(14) NOT NULL auto_increment,
`userid` int(14) NOT NULL default '0',
`friendid` int(14) NOT NULL default '0',
`friendname` varchar(20) NOT NULL default '',
`pending` varchar(14) NOT NULL default 'yes',
`askee` varchar(14) NOT NULL default 'yes',
PRIMARY KEY (`id`)
) TYPE=MyISAM;


INSERT INTO `friends` VALUES (1, 1, 3, 'made_millionaire', 'no', 'no');
INSERT INTO `friends` VALUES (2, 3, 1, 'radioxromance', 'no', 'yes');
INSERT INTO `friends` VALUES (3, 1, 6, 'air_rock', 'no', 'no');
INSERT INTO `friends` VALUES (4, 6, 1, 'radioxromance', 'no', 'yes');
INSERT INTO `friends` VALUES (5, 3, 6, 'air_rock', 'no', 'no');
INSERT INTO `friends` VALUES (6, 6, 3, 'made_millionaire', 'no', 'yes');

this is basically showing how when a user requests friendship it will add them to each others tables and jsut wait to be added... anyway the question is won't this fill up fast? would a seperate table for each users set of friends be best?
thanks for your reply!

using "yes" and "no" may lead to errors...
perhaps a slightly better way is using enum (short for enumerated... i might have spelt that wrong)

basically, enum allows you to restrict the possible values for the field... If you insist on using yes/no for those fields, I would do:

enum['yes','no']

but i noticed you set the fields to varchar(14), so perhaps you plan on holding words...



I guess you have a user table?
Are users allowed to add more than one friend?

Also, I think you can get rid of the ID field and use userid/friendid as a composite primary key... since i can't add the same person twice as a friend (that would be redundant)...


pending, i assume, means that the friend has to acknowledge the user's request to add him/her to the user's list?

Still can't figure out what askee is...

Really, assuming each friend can be found in your user table, the friendname field is redundant here as well. I always like to have an id field in my tables even if, as MadCowDzz suggests, what really defines uniqueness in this table is the userid/friendid composite. There's no saying you can't have two keys. But that's probably a matter of style and some sort of paranoia (on my part). At any rate, I'd keep all user data in a user table and have the friends table simply match ids to one another and perhaps track the pending and askee status. Finally, as MadCowDzz suggests, using an enum field is a good idea; you'll probably want to specify a default of 'yes' in the even that somebody does try to enter something else somehow.

I am still pretty new to php/myslq and databases in general. So please bear with me, I know I'm making mistakes... but, ok yes each user has a seperate table already set up, and they can have as many friends as they want (hence that I am thinking maybe a new table for each user to hold all friend ids might be best). Secondly the askee row is to jsut check that the user is the askee, and cant be the one to verify the friendship (if askee = yes). These values are simply set by the script as it runs and arent user entered values. I will have to see what enumerated is, I dot think I know. Im guessing maybe setting the askee to 0 or 1? Well thanks for the help you guys, and if it's any clearer now maybe you understand my situation. I guess the main question now boils down to should I simply create a new table for each user to hold his friends, and if so, can this be done with php haha. I'm assuming it can although I have not tried yet...
thank you.

oh and the friendname row, I jsut did that cause I didn't want to get the info of the friend from the users table haha. It was a silly msitake, it would so much better to get rid of that cause yes it is redundant.

No, you definitely don't want to create a new table for each user. That quickly becomes a nightmare to administer and just seems a bizarre way to handle this.

Let me back up a bit. My understanding is that you're going to have a community of users. Users within the system can find other users within the system (by searching/browsing) and invite them to be friends. The invited person can log in and say "yes, I'll be your friend" or "screw off, buddy," at which point s/he appears in the asker's friends list (or not).

If that's an accurate characterization of what you're shooting for, here's how I'd approach it:

- One user table containing contact info, etc. Every user in the system goes here, and this is the table users can browse to look for potential friends.

- One friends table that matches user ids from the user table up. So John finds Mary by searching user profiles and clicks a button to invite her to be his friend. This inserts a row into the friends table containing an auto_increment id (optionally), John's user_id, Mary's user_id, and a "status" value that defaults to some value that indicates "pending". When Mary logs in, the script checks the friends table for any "pending" values associated with her user_id, finds the user information for any of these rows, and prints it to her screen. When she clicks to accept or deny the friendship request, the friends table status field is updated accordingly.

That's really all there is to it unless I'm still misunderstanding what you're trying to do. This simple setup allows any user to maintain a list of many friends without requring extra tables.

To clarify what an enum field is, basically, it's a way of validating input. You define an enum field like "fieldname enum('yes','no','maybe') default 'yes'" -- this means that the only values that can be inserted into the field in question are those found in the list you specify. I haven't tested this, but I suspect that if somebody attempts to enter an invalid value, the default is inserted. Whether or not you use an enum field really isn't going to make or break your program, but it is a way of validating input without having to have lookup tables or to rely on the programming logic to do validation (which it doesn't hurt to do anyway).

I've used enum fields before... Its a handy way to make sure that the information in the field is specifically what you want it to be... Like Dhouston described, its a list of values that the field can contain. If you try insert a value that isn't in the list you are given an error...

I would use the enum field for your 'pending' column.. make sure the values are either 'yes' or 'no'... 'true' or 'false'... '0' or '1'... whichever suits you best =)

Dhouston described your situation the same way I had your site pictured. In which case, the tables you originally posted are good... I still don't understand what "askee" represents... its the person who's inviting the other user? in which case wouldn't it be the same as the user_id?

Right, MadCowDzz, I had meant to address the askee thing and suggest that user_id could be used for the purpose mentioned. Good catch.

thanks so much you guys, you are dead on about it all. That's jsut what my site is (and I will be posting a link to it I suppose in the ratings forum, if you would like to check it out, but you can check from here also: www.passedtime.com
I have never had a real website and jsut started learning php over the summer, so be kind! there's no fancy banner or anything (yet) cause I jsut don't have the time to think about it I guess. And yes, there are probably WAY to many errors in it, and things aren't all the way done. ah nevermind, just go check it out.
Second, thank you for the suggestion about the userid being the askee field I REALLY should have thought of that, it's way too simple. I will use that definatly. Ok, so, thanks!

I suspect most of us have beginner sites full of errors and silly things, thecharking, so don't feel bad about that. My first PHP site has some bizarre code that manually parses out the query string because I was so clueless that it didn't occur to me that there might already be a mechanism in place for handling that.