代码之家 › 专栏 › 技术社区 › XstreamINsanity

数据库模式协助(独立于平台)

database-schema cross-platform database-design

XstreamINsanity · 技术社区 · 16 年前

我有一个观点问题,但同时也有一个正确的答案。我正在尝试开发一套产品,并希望确保自我自己动手以来,我第一次就做对了。我已经写了很多次这个模式,每次都认为它更好。然后我会遇到一些新的想法,它要么需要对模式进行大量的工作,要么会破坏我的模式。

在大学里,我学习了“合理化”(我认为这是他们使用的词,可能有点离谱)一个数据库,有5个层次。根据我的记忆,3级是最常见的。我知道这样做的目的是确保数据不会重复,要做到这一点,您必须将表分解成更小的表。根据你把它打破的程度,等级越高。嗯,我不知道我是否想要最高级别的,但我知道我想要的是尽可能高效的。我有4年的SQL Server 2000/2005/2008和2年的Oracle,大约6个月的Informix(5年前),这里或那里的MySQL和大约6个月的访问。我的首选项是SQL Server,但我希望模式在任何一个平台上都一样高效。

这里是一些表的psuedo模式布局,然后我将解释我要做什么。

Manufacturers
  ManufacturerID (Identity)
  ManufacturerName
  ManufacturerStreetAddress
  ManufacturerZipCodeID
  ...

ZipCodes
  ZipCodeID (Identity)
  ZipCode
  ZipCodeStateID
  ...

States
  StateID (Identity)
  StateName
  StateAbbreviation
  ...

Cities
  CityID (Identity)
  CityName
  CityStateID
  ...

我为它只是一个psuedo模式道歉,但这是我现在所拥有的一切,因为我正在纸上休息设计,但在我走得太远之前有一个问题。我要做的是确保每件事都正确地联系在一起。我相信邮政编码属于一个州和一个城市,但没有一个城市属于任何一个邮政编码,它可以有很多。如果我把邮政编码放在制造商的表格中,我希望能够得到州和城市。但我不想在其他表中多次使用任何ID。我的意思是,在邮政编码和城市中使用stateid的次数可能太多了。一个州可以有多个同名城市,多个州可以有多个同名城市。但我不确定是否需要一个citynames表,然后一个citystates表(citynameid和stateid)。我很清楚有一些位置数据库可以购买,也许是一些免费的,我可以使用,不用担心这个。然而,我想努力理解这一点,因为我相信这将有助于我未来的模式设计,同时也因为如果需要更改任何内容,我希望具有布局的可定制性。

问题:

这个psuedo模式是正确的还是更好的(意见)?
它是被称为“合理化”数据库,还是其他什么(将投票赞成正确的答案)?有多远太远了(意见)
还有一个用户表和其他包含地址的表(团队、capitols等),那么,如果psuedo模式在理论上是正确的,那么它对于这样的数据库是一个好的计划吗(意见)?

谢谢大家的时间,我会投票决定任何彻底和连贯的答案。数据库专家或有多年数据库经验者优先,但我会听取所有答案。此外,我不确定这是否应该是一个社区维基,但我现在没有将其标记为一个。谢谢。

更新 :另外,我忘了提到我知道“合理化”数据库需要连接,有时需要子查询。我通常滥用左外部联接,但是将这些表连接在一起以显示地址的最有效方法是什么,而不是执行4个不同的查询?谢谢。

更新 :好的,现在这可能太规范化了,或者还不够规范化,或者根本不够规范化,但是你们能告诉我你们是否更喜欢这个psuedo模式吗?

Manufacturers
  ManufacturerID (Identity)
  ManufacturerName
  ManufacturerStreetAddress
  ManufacturerCCSZID --CCSZ (Country, City, State, Zip), needs a better name
  ...

ZipCodes
  ZipCodeID (Identity)
  ZipCode
  ...

States
  StateID (Identity)
  StateName
  StateAbbreviation
  ...

Cities
  CityID (Identity)
  CityName
  ...

Countries
  CountryID (Identity)
  CountryName
  CountryAbbreviation
  ...

CountryCityStateZipCodes
  CountryCityStateZipCodeID (Identity)
  CCSZCountryID
  CCSZStateID
  CCSZCityID
  CCSZZipCodeID

要得到一个地址,它应该是:

SELECT  M.ManufacturerStreetAddress,
        CN.CountryName,
        CN.CountryAbbreviation,
        S.StateName,
        S.StateAbbreviation,
        C.CityName,
        Z.ZipCode
FROM Manufacturers M
LEFT OUTER JOIN CountryCityStateZipCodes CCSZ ON CCSZ.CountryCityStateZipCodeID = M.ManufacturerCCSZID
LEFT OUTER JOIN Countries CN ON CN.CountryID = CCSZ.CCSZCountryID
LEFT OUTER JOIN States S ON S.StateID = CCSZ.CCSZStateID
LEFT OUTER JOIN Cities C ON C.CityID = CCSZ.CCSZCityID
LEFT OUTER JOIN ZipCodes Z ON Z.ZipCodeID = CCSZ.CCSZZipCodeID

或者也许你们知道一个更好的方法来写这个查询。但是无论如何,这看起来比第一个模式好吗?

3 回复 | 直到 8 年前

JohnK813 16 年前

我一直听到它叫做“正常化”,但我们谈论的是同一件事。

最简单的方法可能是将城市、州和zip合并到一张表中。您甚至可以考虑使用邮政编码本身作为密钥,尽管我可以考虑两个原因来避免这样做:

东北部各州有邮政编码从0开始,这将是如果将邮政编码设为数值字段。
如果使用邮政编码作为密钥,则不能在多个邮政编码中使用该邮政编码。多个城镇的时间。像你一样说,邮局更关心关于邮政编码而不是城镇名称。但是这个设置会限制你从那些人身上寻找以后的城镇。

要以后按城市、州或zip搜索,只需将此表连接到制造商表。您可以使用内部联接-除非manufacturerzipcodeid为空的manufacturers表中有字段,在这种情况下,您还需要一个左联接来显示这些字段。

wadesworld 16 年前

我对你做事的方式没什么问题。邮政编码中的州ID可能很危险——如果知道有邮政编码跨越州边界,我不会感到惊讶,但我不确定。

通过将状态、城市和邮政编码存储在单独的表中,您将要进行大量的连接,但是在处理了存储地址而没有一致性度量的数据库之后,这比一些连接更像是一场噩梦。例如,你以“纽约”、“纽约”、“纽约”、“纽约”和“纽约”结尾。所以我认为,从长远来看,州、市和拉链的桌子是分开的。

Hendra Jaya 16 年前

我不是数据库专家,但在我看来,给定的伪模式似乎不正确。解释如下。从问题中了解到的事实是:

一个州可以有多个城市。
状态是唯一的
一个城市可以有多个邮政编码
城市名称可能等于其他城市名称。
邮政编码是唯一的

首先,写下唯一性。因此,我们构建这两个原始表:

STATE
---
State ID (PK)
State Name

ZIP
---
Zip ID (PK)
Zip Code (NK)

然后,一个合乎逻辑的问题出现了。知道邮政编码,我们如何检索城市编码?要回答这个问题,我们需要在zip和city之间提供一个链接。这个链接应该放在哪里?它不在城市表中,因为事实3我们知道一个城市可以有许多不同的邮政编码。所以它必须在压缩表中。这是我们下一个版本的zip表:

ZIP
---
Zip ID (PK)
Zip Code (NK)
City ID (FK)

现在,既然我们可以从zip“移动”到城市,我们将讨论城市表。城市名称可以与其他城市名称相同。所以我们不需要强制它(城市名称字段)是唯一的。这是我们的第一个版本的城市表格:

CITY
----
City ID (PK)
City Name

同样,同样的逻辑问题出现了。我们该如何去了解一个城市?必须在这两个表之间的某处创建链接。再说一次,知道这一事实并不能保证城市名称的独特性。链接必须放在市桌上。这是我们下一个版本的城市桌:

CITY
---
City ID (PK)
City Name
State ID (FK)

通过这个链接,我们可以正确地检索状态。总的来说,我们可以通过城市ID(在Zip表中提供)从一个城市移动到另一个城市,并且可以通过州ID(在城市表中提供)继续从一个城市移动到另一个州。

从数据库的角度来看,合理化数据库是好的,但从编程的角度来看,它可以被认为是“坏的”。因为它推动程序员编写越来越多的类。毕竟,“太远”可以定义为“表变得不合理”。城市名称表似乎不合理,因为它是一个属性,而不是一个实体。如果我的数据库分析师创建了这样一个不合理的表,我会很高兴地将其标记为“太远了”。另一方面,过度合理化数据库会极大地影响数据库的性能。根据我的经验,它将使查询运行速度变慢。

关于另一个问题,比如用户、团队、Capitols等,我现在什么也说不出来,因为我还没有看到这个问题。