SQL中多对多的表设计以及操作

Question

现在需要做一个资源分享的功能，如：
文件：
A
B
C
好友：
甲
乙
丙
丁
需要的功能是：选择多个文件，同时分享给多个好友
问题1:如果选择的文件数为100个，好友数是200个，这种数据应该怎么建表呢？（如果放在一个表里，需要100*200=2万，数据有没有好的存放方法，如多个表存放，这样数据量太大了）
问题2：如果A文件已经分享给 甲乙丙丁 ，如果我再重复点击分享，是应该提前判断数据库中是否有重复记录好还是先插入一遍，然后批量删除所有重复记录好？困扰2天了，脑子也大了，实在想不通了，望高手指点

Answer 1

文件表：
A
B
C好友表：
甲
乙
丙
丁文件分享表：（两个字段联合唯一）
甲 A
甲 B
乙 C
丁 B

Answer 2

记得在主键组合的后一个键上建一个非聚集索引，比如主键是文件ID，好友ID的话就在好友ID上建索引。方便按好友ID查找好友所拥有的文件。

Answer 3

1、典型的结构文件表（文件id,文件属性等）
好友表（好友id，好友属性等）
文件好友关联表（关联id，文件id（外键关联文件表），好友id（外键关联好友表））
2、先判断重复，查询文件好友关联表中是否存在记录，不存在则创建，否则不创建。

Answer 4

或者按这样的方式存储
好友列         文件列
好友甲        A,B,C,D
好友乙        A,C,D
.            
.
.或者反过来
文件列         好友列
A            甲,乙,丙,丁
B            甲,乙,丙,丁视你的存在情况而定，如果是以文件为主体，那就第二个，否则第一个。那么在处理的时候就要麻烦点了，所以对于第二点建议先删除后插入。

Answer 5

呵呵，你这种方法我也考虑过，但是我用的是GUID做主键，那样，varchar(8000)（我用SQL2000）最多可以200个好友或文件，我不想让他有那种限制

Answer 6

1. 典型的结构：--以下为伪码，省略了数据类型
CREATE TABLE 文件表(
文件id PRIMARY KEY CLUSTERED,
文件属性等
)
CREATE TABLE 好友表(
好友id PRIMARY KEY CLUSTERED,
好友属性等
)
CREATE TABLE 文件分享表(
文件id FOREIGN KEY REFERENCES 文件表(文件id),
好友id FOREIGN KEY REFERENCES 好友表(好友id),
PRIMARY KEY CLUSTERED(文件id,好友id) WITH (IGNORE_DUP_KEY = ON)  --联合主键，忽略重复值
)
CREATE INDEX IX_文件分享表_好友id ON 文件分享表(好友id)2万记录不算多。如果是2万*2万=4亿，可以考虑分区表。
这种结构是性能最高、最方便处理的。
8楼的方案在数据量少的情况下不失为一种紧凑的格式，但数据量大了绝对是灾难。2. 文件分享表的主键是必须的。不应该出现重复记录。
重复点击分享时，如果想显示“该文件已分享”，则需要判断是否已在表中；如果只显示“分享成功”，则直接插入即可，主键的选项WITH (IGNORE_DUP_KEY = ON)可以忽略重复插入的值且不报错。

Answer 7

谢谢您的指点，分区表是咩意思?
----
重复点击分享时，如果想显示“该文件已分享”，则需要判断是否已在表中；如果只显示“分享成功”，则直接插入即可，主键的选项WITH (IGNORE_DUP_KEY = ON)可以忽略重复插入的值且不报错。
----
主键的选项WITH (IGNORE_DUP_KEY = ON)可以忽略重复插入的值且不报错，这样性能怎么样？谢谢

Answer 8

多个文件分享:
建立一个分享表.
分享表,有分享ID.
文件分享表,分享id,文件ID.
好友分享表,分享id,好友id.要查某好友是否分享了某文件,通过分享ID来关联.

Answer 9

关联查询如何来做.查某文件,某好友是否已分享?
select 1 from 好友分计表 where 分享ID in(select 分享ID from 文件分享表 where 文件ID=x)

Answer 10

我的意思是说，100*200=2万根本不算大数据量。
大数据量（记录数千万以上）的表考虑分区表，即通过把表中数据分成多个区，放在不同文件组，均衡IO。不过要SS2005以后才支持。这是在数据写入时直接判断的，比先判断是否存在再插入性能要好。
不过LZ需要查一下这个选项SS2000是不是支持。我不确定这是不是SS2005的新特性。

Answer 11

你看3个表：
如：
文件表：
A
B
好友表
甲
乙
丙您所说的3个表：
关联表：
ID 分享ID
1  1
1  2
文件分享表：
ID 分享ID 文件ID
1  1      1
2  2      2 
好友分享表:
ID 分享ID 好友ID
1  1      1 
2  1      2
3  1      3
4  2      1
5  2      2
6  2      3

Answer 12

LZ应该从自己的系统需求出现，把思路理清楚。如果多个文件可以分享给多个好友，但同一文件对同一好友只分享一次，则好友与文件之间是多对多联系，需要建立分享表（文件ID，好友ID）。类似于收藏功能。如果每次分享可以包含多个文件，每次分享可以分享给多个好友，则分享与文件是多对多联系，分享与好友是多对多联系，（注意：文件与好友没有直接联系），需要建立分享表（分享ID），分享文件表（分享ID，文件ID），分享好友表（分享ID，好友ID）。类似于购书单功能。这种情况下，一个文件可以多次分享给一个好友。考虑极限情况，第2种比第1种的数据量大得多。关键是，LZ想要实现什么样的业务。

Answer 13

从数据库设计的角度来说，我1楼+3楼的方案应该是最正常的，如果怕映射表大可以通过分表或者分区表的方式提高检索速度，如果表中字段很少的话（这里假定只有两到三个短ID字段），加上索引就算上亿条数据也能很快检索出来的。
如果想采用字符串冗余的话，那就要两表都加，不然逆向查找时一定是表扫描。这样的话维护起来就要多一次操作。表空间也会加大。优点就是在检索某文件对应的用户和某用户对应的文件时速度会快，只是输出的字符串需要在客户端进行解析。如果拿不定主意的话，不妨建好测试环境，两种都试一下，对比一下速度和IO，再确定保用哪套方案。

Answer 14

完全赞同。映射表建好索引，在检索某文件对应的用户和某用户对应的文件时速度也会很快。考虑到解析字串的开销，字符串冗余的方案实在没有优势可言。这种方案唯一的好处是查表时看起来直观，初学数据库的人往往习惯这样设计。
采用字符串冗余，而且两表都加，既违反了第一范式，也违反了DRY原则，将来哭都不知道找谁去。

Answer 15

create table t_fenxiang(id int,opr id,rq datetime)create table t_fxbook(id int,id_book int)create table t_fxfriend(id int,id_friend int)
请问如果我要按照这种关系建表，t_fxbook跟t_fxfriend应该建立什么索引呢？
select id_book from t_fxbook a where exists(select 1 from t_fxfriend b where a.id = b.id and b.id_friend='?')  
这是找到这个人的所有的分享的文件．请问这个SQL的意思是？谢谢