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

发布日期:2018-02-09    浏览次数:487

  假设有一个要开发一个试题系统,全是不定项选择题。一道题可能有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)
)

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

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

 

本文网址:https://www.wyxxw.cn/blog-detail-17-18-153.html

返回列表

非特殊说明,本文版权归原作者所有,转载请注明出处

提示:本站所有资源仅供学习与参考,请勿用于商业用途。图片来自互联网~如侵犯您的权益,请联系QQ:1067507709.

提示:转载请注明来自:http://www.cnblogs.com/kissdodog/p/3304523.html 。 转载人:momo