SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Modeling a Friends List

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Quote Originally Posted by le moi
    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:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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



    Quote Originally Posted by DoubleDee View Post
    I'll look, but can you please provide some links to some of the better discussions?
    the search will do you good



    Quote Originally Posted by DoubleDee View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Code:
    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?!)


    Quote Originally Posted by r937 View Post
    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?


    Quote Originally Posted by r937 View Post
    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?


    Quote Originally Posted by r937 View Post
    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...
    Code SQL:
    SELECT *
    FROM friend
    WHERE requestor=19
    UNION
    SELECT *
    FROM friend
    WHERE requestee=19


    Debbie

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,934
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •