逻辑数据库设计 - 多列属性(多列转行)

　　假设有一个要开发一个试题系统，全是不定项选择题。一道题可能有2,3,4...个答案，数据应如何设计呢？本处旨在说明问题所在，例如同类问题还有存储电话，一个人可能有多个号码等等。

一、存储多值属性

　　反模式：创建多个列。

　　我们知道每列最好只存储一个值，因此先看如下设计：

　　CREATE TABLE Question(
　　    QuestionId　int 　　PK,
　　    QuestionBody nvarchar(500),
　　    Answer1　　nvarchar(500),
　　    Answer2　　nvarchar(500),
　　    Answer3　　nvarchar(500),
　　    Answer4　　nvarchar(500)
　　)

　　类似上面的设计，假设答案只有两个，那么后面的两个Answer3,Answer4就为NULL，展示表如下：

　　

　　这是很传统的属性设计，导致即使现在很简单的任务也变得很简单了！

　　1、查询数据

　　假设有一道题的答案出错了，但是你只记得答案。

SELECT * FROM Question WHERE 
    Answer1 = '猪有4条腿'
    OR Answer2 = '猪有4条腿'
    OR Answer3 = '猪有4条腿'
    OR Answer4 = '猪有4条腿'

　　显示结果如下：

　　

　　假如，另外也有一道题目，也有一个答案猪有3条腿，那么你要查得这条数据，就需要这样：

SELECT * FROM Question WHERE 
    (Answer1 = '猪有4条腿' OR Answer2 = '猪有4条腿' OR Answer3 = '猪有4条腿' OR Answer4 = '猪有4条腿')
    AND
    (Answer1 = '猪有3条腿' OR Answer2 = '猪有3条腿' OR Answer3 = '猪有3条腿' OR Answer4 = '猪有3条腿')

　　怎么样又长又臭吧！不怕，哥安慰下你，其实有简单点的方法：

　　SELECT * FROM Question WHERE 
    　　'猪有3条腿' IN (Answer1,Answer2,Answer3,Answer4)
    　　AND
    　　'猪有4条腿' IN (Answer1,Answer2,Answer3,Answer4)

　　怎么样短了很多吧，相信你也还是不会卖帐。

　　2、添加、更新以及删除值

　　在以上设计中，假设我要删除答案'猪有3条腿'的SQL语句怎么写呢？大家被考到了吧。

　　UPDATE Question
   　　 SET Answer1 = NULLIF(Answer1,'猪有3条腿'),
        　　Answer2 = NULLIF(Answer2,'猪有3条腿'),
        　　Answer3 = NULLIF(Answer3,'猪有3条腿'),
        　　Answer4 = NULLIF(Answer4,'猪有5条腿')
　　WHERE QuestionId = 2

　　添加一个答案，更新一个答案都有点难写， 有兴趣的朋友可以自己敲敲。

　　3、确保唯一性

　　如何确保同一个值不出现在多个列中，即有可能你并不想一道题中有两个答案是一样的。我们很难阻止重复的答案出现，因为Unique只能用于行。

　　4、值在不断增长

　　当我们有一道题有5个答案的时候，悲剧，你要更改表结构了。

　　以上种种问题说明，以上设计根本不堪一击。

二、解决方案 - 从属表

　　问题的根源在于：存储一个具有多个值的属性。对于以上设计，如果每道题都规定是4个答案，以上设计是可以用的。问题在于，答案个数不确定。

　　因此，我们需要创建一个从属表,将不确定个数的值提取出来作为行存储，而不是列。

　　总体设计如下：

CREATE TABLE Question(
    QuestionId    int    PK,
    QuestionBody Body nvarchar(500)
)

CREATE TABLE Answer(
    AnswerId    int    PK,
    AnswerBody    nvarchar(500),
    QuestionId    int,
    FOREIGN KEY(QuestionId) REFERENCES Question(QuestionId)
)

　　对于以上设计，多少个答案都没问题了，而且增删查改都简单了不止一个档次。

　　其实，只不过是一个一对多的关系。而且这个问题很容易一眼就看出，不过变种问题你却未必会条件反应式地提出一样从属表。