第8章 多列属性

我已经数不清创建过多少次存储联系人信息的表了。每次都有一些共同的字段，比如名字、称呼、地址、公司等。

电话号码有一点棘手。通常人们会同时拥有多个号码：家庭电话，工作电话，传真号码以及手机号码。在联系人信息表中，分4列来存储这些信息是很简单的方法。

但对于其他号码呢？这个人的助理的号码、另一个移动电话的号码，或者外地办事处的全然不同的电话号码，甚至还有些无法预计的分类。我可以为这些并不常有的情况创建更多的列，但这看上去很笨拙，因为加了很多很少使用的字段。而且，到底要加多少这样的列才够呢？

这是和第2章一样的目标：一个属性看上去虽然只属于一张表，但同时可能会有多个值。之前我们已经看到，将多个值合并在一起并用逗号分隔导致难以对数据进行验证，难以读取或者改变单个值，同时也对聚合公式（诸如统计不同值的数量）非常不友好。

我们将使用一个新的例子来说明这一反模式。我们将让这个Bug数据库允许加入标签，因而就可以以此来分类Bug。某些Bug可能是由它们所影响的软件子系统，比如打印、报表或者邮件来分类的；另一些Bug可能是由它们的类型来分类的，比如一个造成程序崩溃的Bug会被标记为crash，也可以标记为performance来说明性能问题，当然还可以标记cosmetic来说明用户界面的颜色选择不好。

这个给Bug打标签的特性必须支持多标签，因为标签并不会相互排斥。一个Bug可能同时影响到多个子系统，也有可能会影响到子系统的某个特性，比如“打印的性能”。

我们依旧需要考虑一个属性的多个值，但我们知道每列最好只存储一个值。在这张表中创建多个列，每个列只存储一个标签看上去很自然。

Multi-Column/anti/create-table.sql
CREATE TABLE Bugs (
	bug_id SERIAL PRIMARY KEY,
	description VARCHAR (1000),
	tag1 VARCHAR (20),
	tag2 VARCHAR (20),
	tag3 VARCHAR (20)
);

当你将一个标签指定给一个Bug记录时，必须将这个标签存放于这三个列中的一个。其他未使用的列将保持空的状态。

Multi-Column/anti/update.sql

在使用传统属性设计的时候，很简单的任务现在变得更复杂了。

当根据一个给定标签查询所有Bug记录时，你必须搜索所有的三列，因为这个标签字符串可能存放于这三列中的任何一列。

比如，要获取被标记为performance的Bug，需要使用如下的一个查询表达式：

Multi-Column/anti/search.sql
SELECT
	*
FROM
	Bugs
WHERE
	tag1 = 'performance'
OR tag2 = 'performance'
OR tag3 = 'performance';

你还可能需要查找同时被标记为performance和printing的Bug。要完成这样的查询，就需要写如下的查询语句。请注意必须正确地使用括号，因为OR操作比AND操作的优先级要低。

Multi-Column/anti/search-two-tags.sql
SELECT
	*
FROM
	Bugs
WHERE
	(
		tag1 = 'performance'
		OR tag2 = 'performance'
		OR tag3 = 'performance'
	)
AND (
	tag1 = 'printing'
	OR tag2 = 'printing'
	OR tag3 = 'printing'
);

在多个列中查找一个值的语法是冗长乏味的。你可以通过一种非传统的方式来使用IN，从而使得这个查询变得更加精简：

Multi-Column/anti/search-two-tags.sql
SELECT
	*
FROM
	Bugs
WHERE
	'performance' IN (tag1, tag2, tag3)
AND 'printing' IN (tag1, tag2, tag3);

在这个列的集合中添加以及删除一个值也是有问题的。单纯地使用UPDATE语句来更新一列的值是不安全的，因为你无法得知到底哪一列是有值的（如果有的话）。你可能不得不将整行数据读取到前端程序中来分析。

Multi-Column/anti/add-tag-two-step.sql
SELECT * FROM Bugs WHERE bug_id = 3456;

举个例子，假设我们知道tag2是空的。于是写出如下的UPDATE语句。

Multi-Column/anti/add-tag-two-step.sql
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;

这么做，你就要面临多步操作间的数据同步问题，即有可能在你完成一次查询并且更新记录这两步操作之间，有另一个客户端也同时在执行相同的操作。根据更新操作执行顺序的不同，你或者另一个客户端将得到一个“更新冲突”的错误，或者一方覆盖了另一方的数据。你可以使用更复杂的SQL语句来避免这样的问题。

如下的表达式使用了NULLIF()1函数来将每一个等于指定值的列置为空。当传入的两个参数相等时，NULLIF()函数返回空。

Multi-Column/anti/remove-tag.sql
UPDATE Bugs
SET tag1 = NULLIF(tag1, 'performance'),
 tag2 = NULLIF(tag2, 'performance'),
 tag3 = NULLIF(tag3, 'performance')
WHERE
	bug_id = 3456;

如下的表达式将performance标签加到第一个空列中。然而，如果3列都不为空，这条语句将不对这条记录做任何修改，新的标签将不会被记录。同时，写这样的查询语句是非常繁琐耗时的，你必须要重复performance这个字符串6次！

Multi-Column/anti/add-tag.sql
UPDATE Bugs
SET tag1 = CASE WHEN 'performance' IN (tag2, tag3) THEN
	tag1
ELSE
	COALESCE (tag1, 'performance')
END,
 tag2 = CASE WHEN 'performance' IN (tag1, tag3) THEN
	tag2
ELSE
	COALESCE (tag2, 'performance')
END,
 tag3 = CASE WHEN 'performance' IN (tag1, tag2) THEN
	tag3
ELSE
	COALESCE (tag3, 'performance')
END
WHERE
	bug_id = 3456;

你可能并不希望同一个值出现在多个列中，但当你使用多值属性这个反模式时，数据库并不能阻止这样的情况发生。换而言之，很难阻止如下语句的执行：

Multi-Column/anti/insert-duplicate.sql
INSERT INTO Bugs (description, tag1, tag2, tag3)
VALUES
	(
		'printing is slow',
		'printing',
		'performance',
		'performance'
	);

这个设计的另一个弱点在于三列可能并不够用。要保证每一列只存储一个值，你必须定义和一个Bug能支持的标签最大数一样多的列。在定义这张表的时候，你能预计多少标签数量是最大值吗？

有一个策略是暂时先猜测一个中等规模的量并在日后必要时对表进行扩展。大多数数据库允许你对已经存在的表进行重构，因此你可以增加Bug.tag4这个列，或者在需要时增加更多的列。

Multi-Column/anti/alter-table.sql
ALTER TABLE Bugs ADD COLUMN tag4 VARCHAR(20);

然而，这样的改变在以下三点上的开销是巨大的。

Multi-Column/anti/search-four-columns.sql
SELECT
	*
FROM
	Bugs
WHERE
	tag1 = 'performance'
OR tag2 = 'performance'
OR tag3 = 'performance'
OR tag4 = 'performance';
-- you must add this new term

这是一个细致且费时的开发任务。如果你漏掉了任何需要修改的查询语句，就可能造成难以定位的错误。

反模式的模式

乱穿马路和多值属性这两个反模式都有一个共同的主线：这两个反模式都是解决同一个目标的解决方案——存储一个具有多个值的属性。

在乱穿马路的例子中，我们解决了多对多关系的存储。在本章中，我们将看到如何解决一对多的关系。不过需要明白的是，这两个反模式和两种数据间的关系有时是可以互用的。

如果用户界面上或者项目的设计文档中有任何属性可能具有多个值，并且这个值的数量具有一个固定的最大值，这可能意味着你使用了多值属性这个反模式。

诚然，有些属性的确可能为了某种目的，其候选值的数量具有最大值的限制，但通常情况下是没有这种限制的。如果这个限制是任意加上去的或者看上去不太合理，那可能就是因为使用了这个反模式了。

如果你听到有人说了下面这些话，那也是一个线索，提醒你可能使用了本章的反模式：

在某些情况下，一个属性可能有固定数量的候选值，并且对应的存储位置和顺序都是固定的。比如，一个给定的Bug可能和多个用户账号相关，但每个关系的作用都是唯一的：一个是报告这个Bug的用户，另一个是修复这个Bug的开发人员，另一个是验证Bug修复状态的质量控制工程师。即使这几列里存储的值是相似的，它们的作用以及实际的业务逻辑都是不同的。

在Bugs表中定义三个不同的列来存储这三个属性是合理的。本章所描述的那些缺点在这里无关紧要，因为基本上这几列都是分开使用的。虽然有时仍旧需要对所有这三列数据进行查询，比如在一份“每个Bug都涉及哪些人”的报告里就需要这么做。既然这样的设计能够使得大部分的查询用例都变得很简单，仅在有限数量的查询用例上表现得复杂还是能够被接受的。

另一种组织数据的方式是创建一张从属表来存储Bugs表和Accounts表之间的关系，同时在这张额外的表中增加一列来记录一个关系中的账号所表示的角色。然而，这样的设计可能会导致第6章EAV所描述的一些问题。

如同我们在 第2章 乱穿马路 中所看到的那样，最好的解决方案是创建一张从属表，仅使用一列来存储多值属性。将多个值存在多行中而不是多列里。同时，在从属表中定义一个外键，将这个值和Bugs表中的主记录关联起来。

Multi-Column/soln/create-table.sql
CREATE TABLE Tags (
	bug_id BIGINT UNSIGNED NOT NULL tag VARCHAR (20),
	PRIMARY KEY (bug_id, tag),
	FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id)
);
INSERT INTO Tags (bug_id, tag)
VALUES
	(1234, 'crash'),
	(3456, 'printing'),
	(3456, 'performance');

当所有和Bug相关的标签都存储于一列中时，查找和一个给定标签相关的所有Bug就变得很直接了。

Multi-Column/soln/search.sql
SELECT * FROM Bugs JOIN Tags USING (bug_id) WHERE tag = 'performance';

即使更复杂一点的查询，诸如“和两个标签相关的Bug”，其查询代码也非常简单易读。

Multi-Column/soln/search-two-tags.sql
SELECT
	*
FROM
	Bugs
JOIN Tags AS t1 USING (bug_id)
JOIN Tags AS t2 USING (bug_id)
WHERE
	t1.tag = 'printing'
AND t2.tag = 'performance';

你可以使用比多值属性反模式更简单的方法来添加或移除数据间的关系——只要简单地添加或者删除从属表中的记录就行了。不再需要逐列检查是否有空列可以添加记录了。

Multi-Column/soln/insert-delete.sql
INSERT INTO Tags (bug_id, tag) VALUES (1234, 'save');
DELETE FROM Tags WHERE bug_id = 1234 AND tag = 'crash';

主键的约束能够保证不会有重复记录出现。一个给定的标签只能和一个给定的Bug关联一次，如果尝试重复插入，SQL会告诉你错误。

每个Bug不再限制只能打三个标签了，不像在Bugs表中只能加三个tagN的列，现在只要有需要就可以一直加新的标签。