CodeIgniter Forums
Facebook friends data model - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Facebook friends data model (/thread-12388.html)



Facebook friends data model - El Forum - 10-17-2008

[eluser]Daniel H[/eluser]
Hi all,

Any guesses as to the data structure of 'friends'? As a guess I'm working on the following basis:


Code:
CREATE TABLE `friend_request`
(
`id` BIGINT(20) unsigned  AUTO_INCREMENT ,
`user_id` BIGINT(20) unsigned  NOT NULL,
`recipient_user_id` BIGINT(20) unsigned  NOT NULL,
`message` TEXT NOT NULL,
`read` TINYINT(1) DEFAULT 0 NOT NULL,
`date_read` DATETIME,

/*new
accept
deny*/
`status` VARCHAR(50) DEFAULT 'new' NOT NULL,
`abuse` TINYINT(1) DEFAULT 0 NOT NULL,
`abuse_report` TEXT,
`date_created` DATETIME NOT NULL,
`date_created_gmt` DATETIME NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `friend`
(
`user_id_a` BIGINT(20) unsigned  NOT NULL,
`user_id_b` BIGINT(20) unsigned  NOT NULL,
`friend_request_id` BIGINT(20) unsigned  NOT NULL,
`date_created` DATETIME NOT NULL,
`date_created_gmt` DATETIME NOT NULL
) TYPE=MyISAM;


So, a friend request is created; this is accepted or denied. If accepted, a 'friend' record is created with the link being made between user a and b. However, to make things more logical, I'm expecting to write two records to show 'both sides' of the friend relationship, such that a query for user_id_a will yield all their friends without performing another query for user_id_b. On removal of a friend, the two records are then deleted.

Surely there is a more sensible solution to this, a la Facebook? Any ideas?