Welcome Guest, Not a member yet? Register   Sign In
Facebook friends data model
#1

[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?




Theme © iAndrew 2016 - Forum software by © MyBB