假设我有这些记录
ID 1: has attributes A,B,D
ID 2: has attributes B,C
ID 3: has attributes F
ID 4: has attributes C,G
.....(Attributes will not duplicate in the same record)
总估计记录数:〜180,000
属性总数:70,增加
我要做的查询示例:
-
SELECT * from table WHERE (has attribute B)
-
SELECT * from table WHERE (has attributes B & D)
-
SELECT * from table WHERE (has 2 attributes)
-
SELECT * from table WHERE (has >=3 attributes)
-
SELECT count(*) from table WHERE (has attribute B)
最好的数据库体系结构是什么?
设计1:将属性存储为1s&0S
ID|A|B|C|D|E|F|...
1|1|1|0|1|0|0|...
2|0|1|1|0|0|0|...
3|0|0|0|0|0|1|...
问题:
- 当出现新属性时,需要定期添加新列
- 许多冗余数据(0s(,因为超过80%的数据只有1个属性,而少于0.01%的记录将具有8个以上的属性。
设计2:将属性存储为CSV字符串
ID|Attributes
1|A,B,D,
2|B,C,
3|F,
问题:
- 当我做 时的慢速查询
SELECT * from table WHERE attributes LIKE '%B,%' AND attributes LIKE '%D,%'
设计3:每个属性都有自己的表存储记录ID
Table Attribute A
ID
1
4
5
...
Table Attribute B
ID
1
7
10
...
Table Attribute C
ID
2
8
9
...
问题
- 许多表
- 需要定期添加新表格
- 如何做
SELECT * from table WHERE id (appears in exactly 3 tables)
?
这些是我能想到的,请提出任何好的体系结构。
实际上,是最佳的(第三个是最好的(,我建议一个将ID与其属性相关的单个接线表,例如。
ID | attr
1 | A
1 | B
1 | D
2 | B
2 | C
3 | F
4 | C
4 | G
这是最正常的方法。要查看为什么此设计是最佳的,请查看找到具有属性B的所有ID的容易。
SELECT DISTINCT ID
FROM yourTable
WHERE attr = 'B';
找到所有具有两个属性b和d的ID也很简单:
SELECT ID
FROM yourTable
WHERE attr IN ('B', 'D')
GROUP BY ID
HAVING MIN(attr) <> MAX(attr);
您的前两个建议将使编写这些查询更加困难(尝试一下(,通常,将CSV存储在数据库表中是不好的做法。您的第三个建议确实正确地存储了关系,但它不必要地将数据传播到多个表中。
上述查询的一种更通用的形式,可以轻松扩展到任意数量的IDS是:
SELECT ID
FROM yourTable
WHERE attr IN ('B', 'D')
GROUP BY ID
HAVING COUNT(DISTINCT attr) = 2;