SNS 数据库设计

本文节选自《Netkiller Architect 手札》

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 的关系网

原文发布于微信公众号 - Netkiller(netkiller-ebook)

原文发表时间:2016-10-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大数据钻研

MySQL分页性能优化指南

一道面试的问题,当MySQL表中有数据量很大的时候如何做分页。。。。当时只知道在数据量很大的时候可以分表,但不知道不分表时可以怎么做。。。。唉,谁让代理商就那么...

3088
来自专栏乐沙弥的世界

PL/SQL --> DBMS_DDL包的使用

为了便于建立性能良好的PL/SQL程序,Oracle提供了大量的系统包供使用。Oracle提供的这些包扩展并增强了数据库的一些功能,以及突

804
来自专栏杨建荣的学习笔记

使用dbms_metadata生成建表语句(r2笔记97天)

有时候在工作中,可以使用exp/imp得到表的创建语句。 如果想得到关于table,index,constraint的语句,可以考虑使用dbms_metadat...

2183
来自专栏文渊之博

如何根据日志查看删除的数据(转译)

原文地址:https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ...

17210
来自专栏文渊之博

如果正确读取SQL Server中的扩展事件?

    SQL Server中使用扩展事件捕捉所需的信息后,可以选择存放的位置。比如说内存或文件中,但无论存在哪里,其本质都是一个大XML。因此在SQL Ser...

1729
来自专栏Grace development

电商系统设计之商品 (下)

完成上述流程则是完成了一笔交易,经常网上购物的童鞋都懂这个。今天我们讲下从商品系统到交易系统和订单系统的存储过程及其设计上的应该注意的“坑”。

311
来自专栏数据库新发现

Oracle9i新特性-使用DBMS_METADATA包获得对象DDL语句

从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA,可以用于提取对象创建的DDL语句。

762
来自专栏Java学习123

ORACLE数据库日常维护

3378
来自专栏从ORACLE起航,领略精彩的IT技术。

Oracle之SQL优化专题01-查看SQL执行计划的方法3.1 dbms_xplan.display_cursor(null,null,'allstats last')3.2 dbms_xplan.

2985
来自专栏landv

一、K3 WISE 开发插件《K3 WISE常用数据表整理》

3597

扫描关注云+社区