Deal
和DealCategories
。一笔交易可以有很多交易类别。因此正确的方法应该是制作一个具有以下结构的表
DealCategories
:但是,我们的外包团队通过以下方式将多个类别存储在Deal
表中:DealCategoryId (PK)
DealId (FK)
DealCategoryId (FK)
我觉得他们做错了,但是我不知道如何清楚地解释为什么这是不正确的。
我应该如何向他们解释这是错误的?还是我是错的人,这是可以接受的?
#1 楼
是的,这是一个可怕的主意。而不是去:
SELECT Deal.Name, DealCategory.Name
FROM Deal
INNER JOIN
DealCategories ON Deal.DealID = DealCategories.DealID
INNER JOIN
DealCategory ON DealCategories.DealCategoryID = DealCategory.DealCategoryID
WHERE Deal.DealID = 1234
现在您必须去:
SELECT Deal.ID, Deal.Name, DealCategories
FROM Deal
WHERE Deal.DealID = 1234
然后,您需要在应用程序代码中进行一些操作,以将该逗号列表拆分为多个数字,然后分别查询数据库:
这种设计反模式源于对关系建模的完全误解(您不必害怕表。表是您的朋友。使用它们),或者是一种被误导的信念,即用逗号分隔的列表进行拆分会更快在应用程序代码中,它比添加链接表要好(从来没有)。第三种选择是,他们对SQL没有足够的信心/胜任能力,无法设置外键,但是如果是这种情况,则它们与关系模型的设计无关。
SQL Antipatterns(Karwin,2010年)在第15-23页上专门介绍了该反模式的整章(他称之为“ Jaywalking”)。同样,作者在SO上发布了类似问题。他指出的关键点(应用于此示例)是:
查询特定类别中的所有交易相当复杂(解决该问题的最简单方法是使用正则表达式,但是正则表达式本身就是一个问题。)
没有外键关系,您将无法强制执行引用完整性。如果删除DealCategory nr。 #26然后,您必须在应用程序代码中完成每笔交易,以查找对类别#26的引用并将其删除。这是应该在数据层处理的事情,必须在您的应用程序中处理是一件很糟糕的事情。
再次,聚合查询(
COUNT
,SUM
等)从“复杂”到“几乎不可能” '。询问您的开发人员,他们将如何获得所有类别的列表以及该类别中交易数量的清单。经过适当的设计,这是SQL的四行。更新变得更加困难(例如,您的交易分为五类,但是您想删除两个并添加另外三个)。这是经过适当设计的三行SQL。
最终,您将遇到
VARCHAR
列表长度限制。尽管如果您有一个逗号分隔的列表(超过4000个字符),那么无论如何,解析该怪物的速度都会变得很慢。从数据库中拉出一个列表,将其拆分,然后返回到用于另一个查询的数据库在本质上比一个查询要慢。
TLDR:这是一个有缺陷的设计,无法很好地扩展,甚至为最简单的查询引入了额外的复杂性,而且盒子会使您的应用程序变慢。
评论
西蒙,有人问了同样的问题(dba.stackexchange.com/questions/17824/…),但是我不清楚为什么同一张FK和PK在同一张表中,这会阻碍3FN。
– jcho360
2012年5月14日下午14:48
我不确定他们是否想在“交易”和“类别”之间建立多对多关系,或者是某种类别的层次结构。无论哪种方式,这都是主要问题,用逗号分隔的字段而不是链接表是一个坏主意。
–西蒙·里格斯(Simon Righarts)
2012年5月14日22:47
#2 楼
但是,我们的外包团队以这种方式将多个类别存储在Deal表中:
DealId(PK)
DealCategory-在这里,他们存储多个交易ID,并用逗号分隔,例如:18, 25,32。
如果只需要查询给定交易的类别,那实际上是一个好的设计。
但是如果您想知道给定类别中的所有交易,那就太糟糕了。
而且做其他任何事情(例如更新,计数,联接等)也非常困难且容易出错。
非规范化有其地位,但您必须记住它会针对一种查询类型,而您可能要针对相同数据进行所有其他查询。如果您知道您将始终以一种模式进行查询,那么使用非规范化设计可能会给您带来优势。但是,如果您有可能需要更多的查询类型灵活性,请坚持使用规范化的设计。
像其他任何形式的优化一样,您需要先确定要执行哪些查询,然后才能决定是否非规范化是合理的。
评论
您是否真的认为用逗号分隔子ID的字符串有帮助?我的意思是,应用程序必须先读取,然后解析ID并查询所有子级,例如select * from DealCategories,其中DealId在(1,2,3,4,...)中。您在数据库设计方面比我拥有更多的经验,因此在某些情况下,您也许有充分的理由在非常特定的情况下进行这种“极端调整”。我唯一能证明这一点的想法是Deal / DealCategory的选择负载很高。在我看来,这就像一个没有任何数据库设计知识的外包团队,除了创建表之外,还创建了它。
–埃里克·哈特(Erik Hart)
2014年1月1日14:48
@ErikHart,这是非规范化,它可能会有所帮助,但我的观点是,它完全取决于您需要运行的查询。没错,非规范化会使所有查询的性能变差,除非它针对一个查询进行了优化。如果您只需要运行一个查询,而不关心其他查询,那将是双赢。但是这些情况很少见,因为通常我们希望以各种方式灵活地查询数据。
– Bill Karwin
2014年1月1日在16:36
@ErikHart,如果为该外包团队提供了仅包含针对该数据的一个查询的项目规范,则他们可以仅针对该特定查询设计一种优化。换句话说,“您要了,就得到了。”但是,外包提供商没有理由为数据的未来使用做计划-他们按照规范中的说明实施应用程序。
– Bill Karwin
2014年1月1日在16:40
#3 楼
列中的多个值与第一种标准格式相反。也绝对不会提高速度,因为要在数据库中链接表。您必须先阅读和解析一个字符串,然后为“交易”选择所有类别。
实现不良的层次结构?
此外,将DealCategories中的FK转换为另一个DealCategory看起来也像是DealCategories的层次结构/树的不良实现。通过父代ID(所谓的邻接表)关系处理树很麻烦!
检查嵌套集(易于阅读,但难于修改)和闭包表(总体性能最好,但可能)实现层次结构时,内存使用率很高-对于您的DealCategories来说可能不会太多!
评论
你是对的。将逗号分隔的列表存储在数据库列中真的不好吗?简短的答案:是的,这很糟糕。立即对外包团队进行解雇,然后再造成伤害...(-_-)