这就是我设置数据库的方式：
QUESTION ------------------- question_id pk question varchar answer varchar subtopic_id foreign_key MEDIA ------------------- media_id pk file_name varchar question_id foreign_key SUBTOPIC ------------------- subtopic_id pk subtopic varchar topic_id foreign_key TOPIC ------------------- topic_id pk topic varchar level choices(1,2,3)
我的数据库设计得当吗？如果没有,我怎样才能让它变得更好？
编辑：我希望显示一个问题(和图像,如果存在),并将用户的答案与正确答案进行比较.我需要主题和子主题才能显示正确的问题.
Edit2：添加了您的建议.
Edit3：更新了数据库描述.
请看一下这个图：
一些说明：
>级别可以具有唯一(主题1和3)和
共享(主题2)主题.
>一个主题可以有一个或多个
副主题,但副主题不能
属于多个主题.
>子主题可以有一个或多个
问题,但问题属于
只有一个副主题.两个问题(问题2和3)可能具有相同的子主题和主题,但它们的级别可能不同.虽然,主题(主题2)和副主题(子主题2和3)可能属于许多级别(级别1和级别2),但某些子主题仅包含级别1的问题,而其他子主题包含级别2的问题(问题4)(子主题3) .
>一个问题只能有一个答案
那个答案不属于
另一个问题.
>答案(或问题)可以
有一个可选的图像文件,和
该图像文件可以在其他中使用
的问题.
谢谢.
解决方法
您的模型的局限性
这在简单的意义上是标准化的：你已经确定了实体是什么;你需要什么关系;你甚至认为它们是没有识别特征的无意义的东西,你已经将IDENTITY列作为物理键.然后你已经规范化了表格.
这与关系模型或规范化无关.
这是典型的应用程序思维(可理解,因为这就是你所拥有的,你知道功能需求).但这与数据无关.如果您从该角度创建它,您将无法获得数据库,您将获得该应用程序的存储位置.现在,如果应用程序是为一个测验编写并丢弃的话,这是可以接受的.但如果测验将要运行一两个月;如果公司在十年内仍然存在;如果测验会增长;如果将要运行的统计数据将会增长,改善和成熟……那么你将遇到很多问题.每次增强应用程序时,都必须更换其“数据库”.在某些时候,他们将决定应用程序(以及其中包含的“数据库”)太昂贵而无法改变,并且放弃了这两者.
即使应用程序存在的持续时间很短,因为数据集合没有规范化,因此非常有限,RM中的“权力”(在每个商业RDBMS中实现)都会丢失给您.导航(由于sql连接繁琐而无论如何都很麻烦)更加麻烦.例如.从一个孩子到一个祖父母,你被迫得到父母,即使没有得到任何列.用户只能通过应用程序访问数据,它是“关闭”的.目前,有数以千计的报表工具连接到数据库,并允许用户执行各种查询,这些查询尚未确定,而无需等待应用团队实施更改请求：能力丢失了.而且你必须每年左右“重新计算”数据堆.
Nomalisation
正规化产生开放式数据库,允许这种访问而不受限制.
数据的寿命比应用程序长得多.它在应用程序中幸存下来,并且对组织有价值.当应用程序被替换时,他们将提取数据并希望替换应用程序准备好加载数据.如果他们没有使用数据建模器,那么新的应用程序设计师就不会从旧应用程序设计人员的错误中吸取任何教训,并且整体重复.
好的,所以该组织预计明年会存在.好的,所以你要在生产中运行多个测验.好的,所以你雇用了一个数据建模器来监视你的数据集,这样你就不会犯这些错误.优秀.谢谢.我接受.
关系建模203
数据建模(a)完全独立于应用程序和您可能了解的任何功能,以及(b)使用完全不同的技术,应用程序开发人员不知道.所以让我们开始吧.这不是一个正式的教训,重新规范化(这将花费太长时间,理论将让你死亡);这只是在他工作的时候看着模特儿的肩膀.所有你需要做的就是回答问题[每次我发一个问号,请,停下来回答,然后继续],老实说(不是暗示你是不诚实的;只是加强“我不知道”是完全可以接受的,因为它确定了我们必须处理的领域;而明确的肯定和否定使我们能够避免讨论).另外,原谅我,但我会假设答案,只是为了避免其他来回延迟;请指出我的任何错误,我会更正模型.
好吧,你有一些你清楚的实体,所以让我们从它们开始,我认为它们看起来像这个Scribble in the Sand.我故意不使用标准符号,因为我不希望在这个阶段将这些含义纳入其中,因为我们有没有实现这些意义,我们不想向理解标准符号的人(包括我们自己)传达错误的期望.
>每个实体中的行如何唯一地从每隔一行中识别出来？ (这很重要,因为它可以帮助我们验证实体实际上是实体,而不是唐老鸭,不是真实的.)从提供的列中,您将(至少)：
Question.Question
.
媒体？究竟是什么,我要确保没有42张相同的图片？如果我允许的话,当我们的老板对你尖叫时,你会踢我的后端,然后他也会踢它. IDENTITY不会帮助我,它会很高兴地让我插入重复项. FileName是一个很好的标识符. Media.FileName
.
话题 ？你想要101个相同的主题吗？我以为不是.
Topic.Topic
.
Subtopic.Subtopic？不是IDENTITY？不,你很清楚它属于Topic,我已经把Topic作为外键,它有意义;如果它是一个受抚养的孩子,而不是一个独立的孤儿,那么FK就是一个识别关系：
副主题:(主题,副主题).
.
好的,我们有机身.
>实体如何相互关联？
你很清楚Subtopic是Topic的孩子,很好.
.
问题是Subtopic的孩子？我想不是.你真的想要同样的问题“家庭拥有多少辆汽车”可能存在于42个测验中,插入42行？ “在两次测验中永远不会出现同样的问题”是可疑的,所以请不要说出来,除非你打算以书面形式提出.更糟糕的是,让我们说问题是一个非常美丽的问题,那么我们也将有42个媒体条目.如果老板不得不在42个地方改变同样的问题而感到不安,当他这样做时他发现一些图像是古老的,当去年发生重大图像更新时它们没有更新……这是你的后端.
.
我们如何考虑在多个测验中存在问题的可能性？
.
所以问题是独立的.
>回到实体.在我们的讨论中,从第五个单词开始,你一直在说“测验”,我一直在说,但我们没有实体.我无法想象一套松散且重复的问题,我只是重复删除,而没有确定所述问题集.现在你可以说“实际上测验是主题”,但这是非常严格的：每当老板需要为一些新客户添加测验时,他必须重新添加整个主题/副主题/问题集,甚至当他知道他之前已经在现有的测验中输入了那些确切的问题;这就是为什么他赢得了新业务,以及为什么他最终会盈利,你刚刚减少到零.我的后端仍然因为他最后的沮丧而受伤,所以让我们确保他没有其他借口.这让他独立成长,培养和改变测验和主题/副主题,而不重复问题：
.
实体.测验
关键？好吧,它最好有一个索引,以确保我们不向他提供重复的测验.假设我们不想将CHAR(80)密钥带入子节点,并且用户认为它们构成的ShortName CHAR(12)是一个很好的标识符,优于10位数字.
测验：QuizCode
.
好吧,我们的机身足够大,有效载荷.到目前为止,我们有这个Improved Scribble in the Sand.
>哪些实体真正独立,在没有其他实体存在的情况下可以存在;和其余的,只存在于另一个实体的背景下？
媒体仍有点悬而未决.虽然我们排除了重复的FileNames,但我们不允许在多个Question中使用相同的Image.我们允许这样做.我们不想要IDENTITY,我们已经携带问题PK,因此它是(a)依赖和(b)由父母识别.
.
问题PK为CHAR(255)之前很好,但在孩子身上把它作为FK变得愚蠢.你喜欢IDENTITY,并且会有数以百万计的问题.精细.
Question.QuestionId
.
让我们给这些独立实体提供方角,并让子项依赖实体成为圆形.现在我们有了这个almost,but not quite,Entity Relation Diagram.
>让我们完成关系.到目前为止,我们有：
测验可以有很多问题,许多测验中都可以存在一个问题.
问题是Subtopic的孩子,但它是独立的.
媒体仅作为选项存在于问题的上下文中.
.
既然我们生活在一个有秩序的宇宙中,而我们的努力是科学的,那么让我们给它一个孩子一些命令.我想我们现在有资格获得Logical Entity-Relation Diagram.
实线是识别关系;虚线不是.现在,因为我们已经对实体和关系应用了一些标准要求,所以我们可以使用标准符号.随意指出我的错误.
就是这样,我们完成了.我希望我已经传达了这个练习是来回的,这就是为什么它被称为建模.宇航员有独立的私人宿舍,他们可以互相沟通.
————————————————– —————————————-
那是什么 ？您还想要数据模型吗？好的,给我五分钟.请自己喝咖啡.
————————————————– —————————————-
它足够小,我也可以给你物理Data Model.我使用IDEF1X方法建模关系数据库,这是一个标准,数据模型因此在IDEF1X Notation渲染,请检查该链接,如果你需要快速审查符号是什么意思.
.
>多对多关系是一个逻辑概念,并在逻辑模型中绘制.它们在物理模型中实现为关联表.我提供了那个.规范化数据库有更多的表(没什么好害怕的),但每个表的列数更少,没有重复的列(没有更新异常).
>哦,天哪. Topic和SubTopic列很大！我们无法将那些胖外键迁移到Question中. [与商业用户讨论.]好的,他们说只有一百个主题和几千个子主题.不需要NUMERIC(10,0).他们想要在下拉菜单中使用完整的Topic和Subtopic,并且他们同意它必须是唯一的,但是额外的短CHAR(6)代码会很好.
看,它确实来回走动.纸很便宜;与任何人和每个人讨论;改进,纠正,改变,调整,改进,而无需创建单个表或编写一行代码;在你将有一个值得编写代码的模型.别的不是.学习的唯一方法是提出坚实的东西,并将其击退或改变;把所有的错误写在纸上,而不是在数据库中.
请注意,Surrogate键始终是一个附加键,一个附加索引.它们永远不能代替Key(这就是你所拥有的,以及Eddie试图让你思考的东西：你没有防止重复,你只是有一个毫无意义的关键保证行是唯一的,就像电子表格一样;和虚假的安全感).因此,我们需要尽量减少它们,而不是在每张桌子上刻上它们.
我希望我已经在上面展示过,诸如“从不使用代理人”和“总是使用代理人”之类的简单规则太难以讨论.不,仔细建模意味着：理解并认为它们是额外的,而不是替代真正的密钥.仅在必要时使用,并在必要时使用它们.在这里,我设法逃脱了一个IDENTITY.对用户有意义的三个短代码是代理人,但它们有意义; IDENTITY列没有(它们最终具有意义,它们无法支持,这是问题的一部分).
我将把DataTypes留给你.但请记住,varchars和Nullable列强制列可变.如果在索引中使用它真的很慢(每个条目都必须在每次访问时都有一些“解包”,即使是中间级别),所以必须避免,除非你想为老板提供一个慢速数据库.
同样,如果您不希望页面上的行开销在每个UPDATE上移动,则固定长度的列都在周围.这意味着我们不能懒惰和varchar一切.
好的,现在我们有一个容纳火箭燃料的室.
对评论的回应1
从你的上一个数据模型中,如果我从问题表中省略了topicCode会更好吗？在子主题和问题表中包含topicCode是不是觉得多余？
好问题.
(最后一个,第五个是数据模型;第四个是实体关系图;前三个是天空中的馅饼,到达那里.)
>在子项中作为外键迁移的主键不是多余的,它是必需的.
> Subtopic PK是(TopicCode,SubtopicCode),复合键(商业数据库支持,作为关系模型的要求).在Subtopic的水平线上方.
>一些开发人员害怕复合键,因为它们在WHERE子句中需要多个引用(纯粹的懒惰; sql对于连接很麻烦;处理它).
.
>那是因为关系Topic :: Subtopic是一个识别关系,这意味着Parent的PK用于构造Child的PK,形成一个Compound Key.请注意,无论如何,父PK必须作为FK携带在Child中,因此它不是多余的;这是必需的.众所周知,这会大大增加数据库的“功率”或“关系性”,并且大大增加了易用性(高级用户通常比开发人员更擅长使用RDb).
>这就是它被确定为标准的原因：IDEF1X是一种强制执行更严格的关系模型应用的标准;它促进了对所有密钥的深思熟虑,这当然对所谓的关系数据库的“关系性”至关重要.
.
>问题是,FK到Subtopic因此是(TopicCode,SubtopicCode).
>如果你在Topic和SubTopic中使用了ID,那么Question会有(SubtopicId)作为FK到Subtopic,你会失去导航能力和意义.
>使用识别父母(和祖父母)的复合键优于使用ID的一个原因是,例如.当你只需要问题和主题中的列时,你不必从Subtopic中选择SELECT,你可以直接从Question获得TopicCode;而对于ID,你被迫从Subtopic中选择,这不是报告要求的一部分,它是由于糟糕的关系建模造成的;未能实现识别关系,意义重大.只有一个原因是数据堆只有ID作为PK(作为FK携带)比标准化关系数据库执行更多的工作.
>因为有关表格简单而且很小,这可能不是证明关系密钥/ IDENTITY列限制的优越性的好例子;考虑任何大的祖父母::父::子表.
>证明这种优越性/局限性的一个更好的例子,值得考虑的是希望进一步讨论这个问题的任何人,是Data Model from my Advanced Course,因为这个问题是明确解决的.
>另一个原因是失去了意义.例如.用户可以简单地选择TopicCode,SubtopicCode,Question FROM Question,并理解结果集.
对评论的回应2
对评论的回应3
我不明白你如何使用相同的图像文件与不同的问题？
简单的例如. “命名一只不会飞的鸟”和“什么是鸸”都可以使用相同的Emu照片.这是我在上面主帖中的(1)假设.该模型允许它通过在FileName上提供非唯一索引.
您是否应在问题表中包含Media.FileName外键并从Media表中删除QuestionId？
好吧,不.我建模的方式有几个优点.如果它在问题表中,那么一些大药水的时间,它将是空的.你需要一个Filename索引来搜索它们;看它是否被使用;或不;等等.这意味着我们无法索引FileName列(或者我们可以索引它,但由于Nulls索引会很慢).现在它被建模为：
>“选项”或问题的子类型(具有图像的子类型)
>没有空
>快速非唯一索引
它可以设置为图像库(唯一索引).我们知道一个问题可以有零对一的图像.您可以告诉我们您的决定,我将更改模型：
>图像是唯一的文件名;它们只能用于一个问题
>图像是唯一的文件名;它们可用于多个问题
那是什么样的关系？ 1 :: 1？
是.媒体结束是“optonal”.确切地说：一个问题有零或一个媒体.因此,“可能有”是动词短语或关系的标题.
该模型以“自然”的层次结构为基础：父母高于儿童;子类型在同一水平上.
看起来不像其他n :: n或1 :: n关系.
没有n :: n关系. (在逻辑级别有一个;它在物理级别实现为关联表.)
**为什么在QuizQuestion表中包含Answer字段？*
您想在哪里存储用户的答案？
看,你需要Quiz和QuizQuestion表;他们之前是有效的实体,你当时没有看到,但你现在做的.
主题和子主题之间的关系在子主题表中定义.在问题表中,您已包含TopicCode和SubtopicCode.你说(如果我没记错的话)包括TopicCode是好的,因为我可以直接从Question表中访问Topic而不加入Subtopic表.
是.按照上面的(1.Subtopic).
但是,根据您的模型,每当我向DB输入新问题时,我都应该选择一个主题和一个子主题[对于问题,从现有列表中].
嗯,无论如何你必须这样做;该模型只是执行它.我理解(根据数据)这就是你所需要的. One Suptopic可以属于多个主题.所以你必须给它一个Topic和一个Subtopic(特定的组合).
该数据库如何确保子主题属于Subtopic表中描述的某个主题？
你没有看到吗？你能看到Subtopic表已经提供了特定的[无论你插入什么] Topic :: Subtopic组合……而不是其他组合？好的,所以当你添加一个新的问题,并给它一个主题和子主题(这是SubTopic的主键,以及问题中的外键)时,数据库将强制执行FK,使得只有一个主题的组合： ：可以使用SubTopic中存在的子主题.
这是对良好自然关系键的力量的一个小洞察.
这是一个良好的关系模型的美妙之处：它提供了来自单个[正确建模]结构的许多要求.
对评论的回应4
