数据库体系结构:当记录具有不同数量的属性(列)

Question

假设我有这些记录

    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，增加

下面是我要做的查询示例：

1. SELECT * from table WHERE (has attribute B)
2. SELECT * from table WHERE (has attributes B & D)
3. SELECT * from table WHERE (has 2 attributes)
4. SELECT * from table WHERE (has >=3 attributes)
5. 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|...

问题：

1. 新属性出现时，需要定期添加新列。
2. 由于80%以上的数据只有一个属性，而少于0.01%的记录具有8个以上的属性，因此存在大量冗余数据。

设计2:将属性存储为CSV字符串

ID|Attributes
 1|A,B,D,
 2|B,C,
 3|F,

问题：

1. 当我这样做的时候缓慢的查询

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
...

问题

1. 许多表
2. 需要定期添加新表。
3. 如何做SELECT * from table WHERE id (appears in exactly 3 tables)

这些是我能想到的设计，请提出任何好的架构。

Answer 1

实际上，您的设计没有一个是最优的(第三个是最好的)，我推荐一个连接表，它将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的is也是相当简单的：

SELECT ID
FROM yourTable
WHERE attr IN ('B', 'D')
GROUP BY ID
HAVING MIN(attr) <> MAX(attr);

前两项建议将使编写这些查询变得更加困难(尝试一下)，通常将CSV存储在数据库表中是错误的做法。您的第三个建议确实正确地存储了关系，但它不必要地将数据分散到多个表中。

上述查询的一种更一般的形式可以很容易地扩展到任意数量的ID：

SELECT ID
FROM yourTable
WHERE attr IN ('B', 'D')
GROUP BY ID
HAVING COUNT(DISTINCT attr) = 2;