mysql - Database Smell - 使用多个表改进当前设计

Question

我正在创建我的技术维基网站的第二个版本，我想改进的其中一件事是数据库设计。问题(或者我认为)是要显示每个文档，我需要加入 15 个以上的表。我有一堆查找表，其中包含与每个 wiki 条目相关的描述性数据，例如使用的程序员、cpu、标签、外围设备、PCB 布局软件、难度级别等。

这是一个布局示例:

    doc
--------------
id   | author_id   |    doc_type_id      .....
1    | 8           |        1
2    | 11          |        3
3    | 13          |        3

_

    lookup_programmer
--------------
doc_id   | programmer_id     
1        | 1                    
1        | 3                     
2        | 2                   

_

     programmer
--------------
programmer_id   | programmer      
1               | USBtinyISP        
2               | PICkit              
3               | .....              

由于某些文档 ID 的单个属性(例如程序员)可能有多个条目，因此我创建了数据库来弥补这一点。其他 10 个属性的布局与上面的 2 个 programmer 表类似。要显示单个文档文章，需要连接大约 20 个表。

我使用 Sphinx 搜索引擎来查找具有某些特征的文章。本质上，Sphinx 索引所有数据(不存储)并根据提供的过滤器返回感兴趣的 wiki 文档 ID。如果我要查找使用某个程序员的文章，然后按日期排序，MYSQL必须先将ALL文档与2个程序员表进行join，然后过滤，最后将剩余的按插入时间排序。没有索引可以帮助我对过滤结果进行排序(使用 150k 文档 ID 需要很长时间)，因为它是在临时表中完成的。可以想象，随着需要过滤的参数越来越多，情况会很快变得更糟。

这是因为我必须依靠 Sphinx 来返回——比如所有使用特定 CPU 和程序员的 wiki 条目——这让我相信我当前的设置有数据库的味道....

编辑:看起来我已经实现了一个[实体-属性-值模型] 1

Answer 1

我在这里没有看到任何表明您已实现 EAV 的信息。相反，看起来您已经为每个表中的每一行分配了一个 ID 号。这是增加连接数的可靠方法，并且它与规范化无关。 (没有“我现在添加了一个身份证号码”的正常形式。)

选择一个查找表。 (我将在示例中使用“程序员”。)不要这样构建它。

create table programmer (
  programmer_id integer primary key,
  programmer varchar(20) not null,
  primary key (programmer_id),
  unique key (programmer)
);

相反，像这样构建它。

create table programmer (
  programmer varchar(20) not null,
  primary key (programmer)
);

在引用它的表中，考虑级联更新和删除。

create table lookup_programmer (
  doc_id integer not null,
  programmer varchar(20) not null,
  primary key (doc_id, programmer),
  foreign key (doc_id) references doc (id) 
    on delete cascade,
  foreign key (programmer) references programmer (programmer)
    on update cascade on delete cascade
);

你有什么收获？您保留了外键引用为您提供的所有数据完整性，您的行更具可读性，并且您消除了连接。以这种方式构建所有“查找”表，并消除每个查找表的一个连接。 (除非您有数百万行，否则您可能不会看到任何性能下降。)