Modeling a Friends List

[DoubleDee]

Now that I have created Member Profiles, and they are viewable by others, I want the ability for a Member to be able to add other Members to their "Friends List".

There will be a need for the person receiving the "Friend Request" to accept the request before a link is established. Also, both the "requestor" and "requestee" need a way to sever the "friendship" if they so choose.


Anyways, I could use some help setting up my Tables, Fields, and Relationships!!


Here is what I have come up with so far, but I'm really not sure how to do things...

Relationships:

One and only one USER (requestor) may have zero or more FRIENDS
One and only one USER (requestee) may have zero or more FRIENDS

user (requestor) -||-----0<- friend ->0------||- user (requestee)



Tables:

user (requestor)
--------
- id
- name


friend
--------
- requestee_id
- requestor_id
- requested_on
- approved_on
- declined_on


user (requestee)
--------
- id
- name


Basically I am trying to mimic what a lot of other Forums - including SitePoint - offer their membership.

Comments and suggestions welcome.

Thanks,


Debbie

[r937]

you aren't suggesting that user(requestor) and user(requestee) are separate tables, are you?

does it matter which of them does the requesting? does it matter when this happened? if so, how do you plan to show that on your site? (you say you want to mimic what other forums do, well, i've never seen this)

[DoubleDee]

you aren't suggesting that user(requestor) and user(requestee) are separate tables, are you?

I'm showing a Logical Model, and not the Physical Model.

does it matter which of them does the requesting?

I don't follow you?!

Just like in real life, on person would initiate the request ("Hey, would you like to be my friend?") and the other person would respond to the request ("Sure, I'd like that!"/"No way, you loser?!")

I am calling the person who initiates the Friend Request as the "Requestor" and the person receiving the Friend Request as the "Requestee".

Both people would presumably come from my "User" table.

I suppose two people could simultaneously, but independently make similar requests.

- Person A requests to make Person B a friend

- Person B coincidentally requests to make Person A a friend

Is that what you meant?

does it matter when this happened?

If a "requestee" doesn't respond "Yay/Nay" in a certain period of time, I suppose the "request" should expire...

if so, how do you plan to show that on your site? (you say you want to mimic what other forums do, well, i've never seen this)

Well, what does SitePoint do?

Lemme describe in words how I envision things working...

Person A and Person B talk and hit it off in the Forums and via PM's. Person A sends Person B a "Friend Request" which creates a record in the "friend" table linking these two people. By default Person A has "accepted" his/her request since he/she made it?! The "Friendship" would not become effective until Person B also "accepts" the request. At that time you have a completed "Friendship".

If the "Friendship Request" is not responded to by both parties in 14 days then it "expires" and while the request may persist in the database, it is no longer valid.

After a "Friendship" has been created, at anytime in the future, either party (i.e. "Requestor" or "Requestee") can end the relationship. (No lawyers or judge is required!!) In this case, either "Friend" would go into his/her "Friend List" and click on something which changes the "Friendship Status" from "accepted" to "declined", at which point things end. (Presumably once a "Friendship Request" is made, the record always exists, but the record can "expire" as described before, OR it can become "inactive" - and therefore not restorable.

Whew!! I feel like I just described the final project in a PHP class?!

Maybe I am making things too intricate or complicated, but that is sorta hat I am shooting for, and I believe it is pretty close to how SitePoint does things...

Thanks,


Debbie

[r937]

does it matter which of them does the requesting?

I don't follow you?!

if user 9 requests a friendship from user 37, you create a row in the table with 9 and 37 as requestor and requestee

if user 37 requests a friendship from user 9, you create a row in the table with 37 and 9 as requestor and requestee

so far, no prob

later, when you do a search, let's say for user 9's friends, your query will look like this:

WHERE requestor = 9 OR requestee = 9

that's going to be problematic because mysql cannot optimize that query, and will do a table scan

there are two ways around this, though

one is to split the WHERE clause and write two queries that are UNIONed

the other is to enter two rows into the table, where 9 and 37 are present twice, in two columns (although these cannot now be called "requestor" and "requestee" and you'll need to find another way to record who asked whom)

debbie, there are literally dozens of previous threads in the sitepoint forums on the friends relationship and how to store the data and retrieve/display it

i suggest that you read some of them

[DoubleDee]

if user 9 requests a friendship from user 37, you create a row in the table with 9 and 37 as requestor and requestee

if user 37 requests a friendship from user 9, you create a row in the table with 37 and 9 as requestor and requestee

so far, no prob

Except there aren't 2 Friendships...

debbie, there are literally dozens of previous threads in the sitepoint forums on the friends relationship and how to store the data and retrieve/display it

i suggest that you read some of them

I'll look, but can you please provide some links to some of the better discussions?


BTW, is trying to create what I described more of a "Database-issue" or a "PHP/Programming-issue"?

Thanks,


Debbie

[r937]

Except there aren't 2 Friendships...

of course not

each friendship is just recorded twice, so that if you want user 9's friends, you just pull friend2 where friend1=9, which is more efficient than running 2 queries and unioning them

usually, fast performance trumps extra disk space

I'll look, but can you please provide some links to some of the better discussions?

the search will do you good

BTW, is trying to create what I described more of a "Database-issue" or a "PHP/Programming-issue"?

in my opinion, the former is the dog, the latter is the tail, but they are of course connected

[DoubleDee]

if user 9 requests a friendship from user 37, you create a row in the table with 9 and 37 as requestor and requestee

if user 37 requests a friendship from user 9, you create a row in the table with 37 and 9 as requestor and requestee

so far, no prob

later, when you do a search, let's say for user 9's friends, your query will look like this:

WHERE requestor = 9 OR requestee = 9

that's going to be problematic because mysql cannot optimize that query, and will do a table scan

Since I am not a Facebook, how big of a deal is it to be doing Full-Table Scans on my "friend" table from a performance standpoint? (The table is skinny, so I can't see it being a big deal?!)

there are two ways around this, though

one is to split the WHERE clause and write two queries that are UNIONed

And how does running a UNION query compare performance-wise to running a Full-Table Scan on a skinny table like my "friend" table?

the other is to enter two rows into the table, where 9 and 37 are present twice, in two columns (although these cannot now be called "requestor" and "requestee" and you'll need to find another way to record who asked whom)

And when and how would you do this?

When one person makes a request, would you INSERT two records then?

debbie, there are literally dozens of previous threads in the sitepoint forums on the friends relationship and how to store the data and retrieve/display it

i suggest that you read some of them

I haven't found anything good so far...


---------------
One important thing I think you are leaving out of the conversation is the concept of "Approving/Declining" a Request. And I think that plays into by the process-flow and whether or not to use 1 or 2 records.

So far, I am leaning towards having ONE Friendship Record and just running a query like this to get the results...

SELECT *
FROM friend
WHERE requestor=19
UNION
SELECT *
FROM friend
WHERE requestee=19

Debbie

[r937]

somebody else should really jump in here

too many database threads recently where you just pump, and pump, and pump me for free consulting

with not so much as a "thank you" along the way

so that's it from me for a while

[DoubleDee]

somebody else should really jump in here

too many database threads recently where you just pump, and pump, and pump me for free consulting

with not so much as a "thank you" along the way

so that's it from me for a while

Back to your old ways already I see...

I didn't realize that it was a sin to ask follow-up questions and clarifications to the advice you offered.

And if you look, I do thank you (and others) for your help.

You are a VERY capricious person...


Debbie