Social Networking Help

[Superian]

I am trying to enable the users to add friends, so would someone please explain to me in detail about what I am missing? What do I need to take into consideration when designing the database? Below are two tables that I have created so far. Any new ideas?

 

CREATE TABLE user(
userID int(11) NOT NULL auto_increment,
friendsID int(11) default ' ',
PRIMARY KEY(userID)
);

CREATE TABLE friends(
friendsID int(11) NOT NULL auto_increment,
userID int(11) default ' '
);

[obsidian]

Here is how I have set up this sort of thing in the past:

USERS:
==========
user_id
username
etc...

FRIEND_REQUESTS:
================
from_id -- references USERS(user_id)
to_id -- references USERS(user_id)
status ENUM('pending', 'approved', 'denied')
PRIMARY KEY(from_id, to_id)

 

With this sort of setup, you can easily query someone's pending friend requests, but you also have control on how to treat the different requests based on their status. There is a ton more you can do with this type of thing, but hopefully this at least gives you a head start on it.

[Superian]

Here is how I have set up this sort of thing in the past:

USERS:
==========
user_id
username
etc...

FRIEND_REQUESTS:
================
from_id -- references USERS(user_id)
to_id -- references USERS(user_id)
status ENUM('pending', 'approved', 'denied')
PRIMARY KEY(from_id, to_id)

 

With this sort of setup, you can easily query someone's pending friend requests, but you also have control on how to treat the different requests based on their status. There is a ton more you can do with this type of thing, but hopefully this at least gives you a head start on it.

 

The friends table would look something like the following, correct?

CREATE TABLE FRIEND_REQUESTS(
from_id int(11), 
to_id int(11), 
status ENUM('pending', 'approved', 'denied')
PRIMARY KEY(from_id, to_id)
);

[obsidian]

The friends table would look something like the following, correct?

CREATE TABLE FRIEND_REQUESTS(
from_id int(11), 
to_id int(11), 
status ENUM('pending', 'approved', 'denied')
PRIMARY KEY(from_id, to_id)
);

 

Yes, but you could even do unsigned for your ids to allow for even more room:

CREATE TABLE friend_requests(
  from_id integer unsigned REFERENCES users(user_id),
  to_id integer unsigned REFERENCES users(user_id),
  status ENUM('pending', 'approved', 'denied') DEFAULT 'pending',
  PRIMARY KEY(from_id, to_id)
);

[Superian]

Inside of the user's table there would be a column if approved, correct?

 

USERS:
==========
user_id
username
etc...

[obsidian]

Inside of the user's table there would be a column if approved, correct?

 

USERS:
==========
user_id
username
etc...

 

If you are referring to a column in the user's table to hold approved friend requests, I'd recommend against it. If you are talking about approved users themselves, yes, you need to know if they are valid users.

[Superian]

Inside of the user's table there would be a column if approved, correct?

 

USERS:
==========
user_id
username
etc...

 

If you are referring to a column in the user's table to hold approved friend requests, I'd recommend against it. If you are talking about approved users themselves, yes, you need to know if they are valid users.

 

Yes, I am talking about users that have already registered. Ok, where would be the best location to store approved friends?

[obsidian]

Yes, I am talking about users that have already registered. Ok, where would be the best location to store approved friends?

 

You could either move accepted requests into an actual friends table or you could query against the requests and just purge out the denied ones periodically. It really comes down to just how busy you think your server will be and how optimized you want your data to be.