Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Email

4.21. SNS 資料庫設計

這裡講解SNS交友社區的資料庫設計與實現

我們要實現下面幾個功能

  1. 朋友之間的關係,多對多關係
  2. 朋友之間的維度,如3度4度....
  3. 朋友的查找
		
CREATE DATABASE `sns` /*!40100 COLLATE 'utf8_general_ci' */
		
		

4.21.1. people 表

people 是存儲人,你可以用為user,member都可以

			
CREATE TABLE `people` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
			
			

存儲具體的這人

4.21.2. firend 表

這個表的功能主要是維持朋友之間的關係網,這裡使用了多對多方式並且使用外鍵防止產生臟數據。

			
CREATE TABLE `friend` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NOT NULL,
	`friend_id` INT(10) UNSIGNED NOT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `unique` (`people_id`, `friend_id`),
	INDEX `FK_firend_people` (`people_id`),
	INDEX `FK_firend_people_2` (`friend_id`),
	CONSTRAINT `FK_firend_people` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_firend_people_2` FOREIGN KEY (`friend_id`) REFERENCES `people` (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

			
			

4.21.3. 演示

首先初始化用戶數據

			
INSERT INTO `people` (`id`, `name`) VALUES
	(1, 'Neo'),
	(2, 'Luke'),
	(3, 'Jack'),
	(4, 'Joey'),
	(5, 'Jam'),
	(6, 'John');
			
			

建立朋友之間的關係

			
INSERT INTO `friend` (`id`, `people_id`, `friend_id`) VALUES
	(1, 1, 2),
	(2, 1, 3),
	(3, 1, 4),
	(4, 1, 5),
	(5, 1, 6),
	(6, 2, 1),
	(7, 2, 3);
			
			

現在就可以查找你的朋友了

			
select people.* from friend, people where friend.people_id = 1 and friend.friend_id = people.id;
			
			

查找朋友的朋友就比較麻煩了,必須使用遞歸方法,一層一層查下去,反覆執行SQL效率是很低的,所以我們準備了第三張表。

4.21.4. network 表

關係網表,主要功能是彌補firend表,用於快速檢索(在不使用遞歸的情況下)

			
CREATE TABLE `network` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NOT NULL,
	`following_id` INT(10) UNSIGNED NOT NULL,
	`friend_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`degrees` VARCHAR(250) NOT NULL,
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `unique` (`people_id`, `friend_id`, `following_id`),
	INDEX `FK_firend_people` (`people_id`),
	INDEX `FK_firend_people_2` (`friend_id`),
	INDEX `FK_friend_people_following_id` (`following_id`),
	CONSTRAINT `FK_firend_people` FOREIGN KEY (`people_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_friend_people_following_id` FOREIGN KEY (`following_id`) REFERENCES `people` (`id`),
	CONSTRAINT `FK_friend_people_friend_id` FOREIGN KEY (`friend_id`) REFERENCES `people` (`id`)
)
COMMENT='Social Network Site - Six Degrees of Separation - http://www.netkiller.cn'
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

			
			

following 一個朋友, Neo following Jam

			
INSERT INTO `people` (`id`, `name`) VALUES
	(1, 'Neo'),
	(2, 'Luke'),
	(3, 'Jack'),
	(4, 'Joey'),
	(5, 'Jam'),
	(6, 'John');

INSERT INTO `network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ( 1, 5, NULL, '1.5');
			
			

之前Neo已經 following Jam,接下來查找Jam的朋友,現在Neo following John, John 是 Jam 的朋友,friend_id = NULL 表示 Jam 尚未有朋友

			
select * from network where people_id=1 and friend_id = 5;

INSERT INTO `sns`.`network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ('1', '6', '5', '1.5.6');
			
			

Neo following Joey, Joey 是 Luke 的朋友, 所以 Luke可能是 Neo的朋友

			
INSERT INTO `sns`.`network` (`people_id`, `following_id`, `friend_id`, `degrees`) VALUES ('1', '4', '2', '1.2.4');
			
			

查詢不同維度下的所有好友,查詢出的用戶ID需要處理。

			
select * from network where people_id=1 and degrees like "1.%";
select * from network where people_id=1 and degrees like "1.2%";
select * from network where people_id=1 and degrees like "1.2.%";
			
			

至此社區管理網就建立起來了

上面的例子演示了 people_id=1 即 Neo 的關係網