need input on facebook-like friend relationships and how to join

sfullman

this is a general discussion

suppose I have a table

addr_contacts

ID
firstname
lastname
email
address
..etc..

with a large number of records

I have a database of business owners that are part of a referral group (i.e. they pass leads to each other). They would all like to import "their" contacts into the database, but keep them as "their" contacts. However, I would like to introduce ways that any contact can look through the listing of other business owners and say "I like this person", and define a friend-like relationship to "that other" business owner as well.

There are 3 more criteria which must be met to do what I'm wanting to do:
1. also the contact must be able to say (which is not a facebook-type relationship), "I want to be friends with EVERYONE", i.e. all business owners - I would like this implicit AND NOT by a join to everyone in the database as that's clumsy and becomes inaccurate when new members are added
2. I want to retain the fact that originally contact A was imported by business owner A, even though contact A could "un-friend" business owner A and still be friends with everyone.
3. I also want contact A (from owner A) to be able to friend contact B (from owner 😎 directly. In other words anyone can be anyone's friend (like facebook)

Note that anyone be or become a business owner. I should explain that business ownership is defined by joining to a second clients table, and is not defined in the contacts table. The contacts table is a universal list of "people objects" not companies.

MY IDEAS SO FAR
for item 1, I need a field in addr_contacts.FriendAllowance with three values:
0=only friends that I make
1=all business owners (I like all these guys)
2=all business owners + everyone else (allows for general forum participation)

for item 2, I need a field in addr_contacts.Importer_ID = addr_contacts.ID of the contact (bus. owner) who did the initial import

for item 3, a second table:

addr_ContactsContacts
------------------------------
ID {primary key , for interactions to key back to}
ParentContacts_ID {the person initiating, e.g. a business owner or the "friender"}
ChildContacts_ID {the person responding, e.g. a contact or "friendee"}
Relationship

Relationship would have 3 values:
0=unfriended (regardless of import or independent friending)
1=importee
2=friend (higher level than an importee)

johanafm

If you're talking about a link table, then of

Relationship would have 3 values:
0=unfriended (regardless of import or independent friending)
1=importee
2=friend (higher level than an importee)

Only values 1 and 2 make sense, since 0 should be "no record present in link table".

In the business table, you stick a "friends_with_all" field, which can be 0 or 1 (or boolean type if supported by database). If it's 0, you will not use the link table to retrieve friends, you just perform a select directly on the contacts table to retrieve all records (where id is not the viewer's contact id).
If it's 1, you select from addr_ContactsContacts and inner join with the contacts table.

Even if friends_with_all is 1, you will still allow the business to add and remove records in addr_ContactsContacts, but they will have no discernable effect until the business once again sets friends_with_all to 0.

sfullman

I agree with you that the "friends_of_all" field must necessarily be a separate field, but I believe having the links entry be 0 does have value. I guarantee you (well, I bet anyway) that Facebook stores the fact that there WAS a relationship even after one party unfriends, and they probably store whether it was the friender or friendee who initiated the action.

There is probably a separate table that stores activity between friends. I would imagine that that table keys off the primary key of this links table

addr_ContactsContacts

vs the keys of the two individuals. Setting to unfriended in this table could "hide" friend activity by appropriate query.

Again, this post is for discussion.

Another condition that could be accommodated by friends_of_all=1 AND individual _ContactsContacts relationships statuses being zero would be "friends with everyone EXCEPT FOR this person(s)"

essentially, addr_ContactsContacts.Relationship represents a "ranking" of one person by another person. Sme of you smart guys might then explain to me that two reciprocal entries might be entered between the two persons, but again, this is a discussion..

johanafm

sfullman;10978905 wrote:
I agree with you that the "friends_of_all" field must necessarily be a separate field, but I believe having the links entry be 0 does have value.

It is, or at least, can be useful. It all comes down to what you want to be able to do. For example, if you wish to somewhere show "You are no longer friends with Y" or "X is no longer friends with Y", you could write this information directly into a log table in which all presentational data is kept (i.e. the facebook wall), to minimize table joins and lookups to determine what to show and making it possible to enable a user to remove something from "the wall" while still keeping the action.

I.e. logic is performed once, then written to "log table" from which all such presentational entries will henceforth be retrieved. In doing so, you might also remove the "friend link" from the link table when someone removes another person as friend.

However, even if you do find the use of a log table useful, you could obviously still keep friends=0 in the link table, although it would only be of use without the suggested log table unless you also want to be able to later look at friend status changes outside "the wall".

But if you want to be able to keep track of changes, you might even take things one step further and keep a table log. Do note that this is not the same type of log as I described above. That one I simply called log, while I call this a table log. The purpose of the table log would be to track all changes to one specific table. For example,

TABLE friends_log
--------------------

p_id	ref_id	action	action_time
1	2		1		2011-04-01 15:00
2	1		1		2011-04-01 15:10
1	2		0		2011-04-01 15:15
1	2		1		2011-04-02 15:00

Which might be interpreted as
Person 1 added person 2 as friend on april 1st, 15:00.
Person 2 added person 1 as friend on april 1st, 15:10, thus making them "reciprocal friends".
Person 1 removed person 2 as friend on april 1st, 15:15.
Person 1 once again added person 2 as friend.

You just have to decide how much historical information you want to keep tracking, and decide how you need to store information according to how often you are likely to need to find certain things out. For example. if you "almost never" need to see wether a pair has ever been friends before, or at what time they dropped friends status, the above table log would be enough and you could drop the entry from the link table. But if you want to be able to find this out often, it would be better to keep the entry in the link table so that you get all this information in one query, no matter if they're friends (value = 1), were friends (value = 0) or never have been friends (no rows in result set).

In a similar manner you'd have to decide on the log needs. If you only ever care about what happened last, keeping the data in the link table only is enough. If history might sometime be needed, you'd oviously need a table log.

sfullman;10978905 wrote:
Another condition that could be accommodated by friends_of_all=1 AND individual _ContactsContacts relationships statuses being zero would be "friends with everyone EXCEPT FOR this person(s)"

Most certainly, but in that case you can't also use a zero value also as "not actively a friend of" (same as never having been friends), since you have now given it a stronger meaning "actively not a friend of" (or "unfriend of" if you prefer). How would you otherwise allow someone to go back to status quo after having been friends with someone?

sfullman;10978905 wrote:
essentially, addr_ContactsContacts.Relationship represents a "ranking" of one person by another person. Sme of you smart guys might then explain to me that two reciprocal entries might be entered between the two persons, but again, this is a discussion..

I would still advice you to keep entries for both people separate. Otherwise you have unnormalized data and will run into problems when performing queries, linking against other tables etc. For example listing a persons friends might look like this

SELECT name, face_image, acc.friend_status AS friend_status_one, acc2.friend_status_two
FROM user
	LEFT JOIN addr_ContactsContacts acc ON acc.person_one = user.id
	LEFT JOIN addr_ContactsContacts acc2 ON acc.person_two = user.id

rather than

SELECT name, face_image
FROM user
INNER JOIN addr_ContactsContacts acc ON acc.ref_id = user.id AND acc.status = 1