is my method correct ?

[Awah Mohamed]

hey people
i am designing a website in php/mysql . well it is a social network. simple one. but i didnt know how to allow people to add friends . so i found the easiest way for me is to create a separate mysql table for each member containing all his/her friends.
but i am not sure if this will be fine.
couz lets say if my site got about 1000 new visiter per day. this will be 1000 new mysql table . is it realy going to be fine ??
if not. please tell me another way that can be better.

[jkon]

Well data model isn’t something you decide through programming it should be set on a analysis base (if you are in a large scale project between demand department and analysis, programmer and feedback and if you are your own when you design the needs of your application before even write a single line of code). In case … a “friend” might have a USER_ID and the subject also could have a USER_ID (or just ID in the users table) so a table with relevance to both should be the logic thing to do. If we are talking about OOP PHP the most straight forwarding thing is that the object user also has a friendsList that it is a list (e.g. array) of user objects (filling them with only with the attributes that the main “user” object use)

[Awah Mohamed]

thanks jkon

[HG&C]

for ease of sql queries and such I would make a table called friends with only 3 fields: id, friend1, friend2 (although you could name it more appropriately)

anyways for each and every user/friend store their id in friend1 and their friend's id in friend2. this means that each time a friend is added you add 2 entries to the friends table.

The reason to do this is for simple SQL queries. when you load someone's profile you will only have to do a SELECT friend2 FROM friends WHERE friend1 = {user's ID} to get a full list of that users friends.

[Awah Mohamed]

thanks alot HG&C.
your answer helped me more

[HG&C]

You are quite welcome, there is a field of DB study called "normalization" that explains how you should organize your tables. This can be very helpful.

[mschroeder]

for ease of sql queries and such I would make a table called friends with only 3 fields: id, friend1, friend2 (although you could name it more appropriately)

anyways for each and every user/friend store their id in friend1 and their friend's id in friend2. this means that each time a friend is added you add 2 entries to the friends table.

The reason to do this is for simple SQL queries. when you load someone's profile you will only have to do a SELECT friend2 FROM friends WHERE friend1 = {user's ID} to get a full list of that users friends.

Actually to properly normalize and design this table, one would not have a separate `id` column. This simply limits the amount of data in the table and makes it possible to have duplicate friend relationships.

CREATE TABLE IF NOT EXISTS `friends` (
  `UserId` int(11) NOT NULL,
  `FriendId` int(11) NOT NULL,
  PRIMARY KEY (`UserId`,`FriendId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As this demonstrates, drop the id column and make your primary key a composite of the Userid and FriendId columns. This prevents UserId 1 from having the same friend connection twice.