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