数据库模式的帮助（平台无关）

Question

数据库模式的帮助（平台无关）

database-designcross-platformdatabase-schema

3

我有一个观点问题，但同时也可能有正确答案。我正在尝试开发一套产品，并希望确保由于我是自己做的，第一次就做对了。我已经多次重写模式，每次都认为它更好。然后我会遇到一些新想法，要么需要在模式上花费大量工作，要么会破坏我的模式。

在大学里，我学到了关于“合理化”（我想这是他们使用的词，可能会完全不同）数据库的知识，有5个级别。据我所知，第3级最常见。我知道实践是确保数据不重复，为此，您必须将表拆分为较小的表。根据您拆分的程度，级别越高。我不知道我是否想要最高级别，但我知道我希望它尽可能高效。我有4年的SQL Server 2000/2005/2008和2年的Oracle，在Informix（5年前）方面接触过6个月左右，这里或那里碰到过mySQL，以及大约6个月的Access。我更喜欢SQL Server，但我希望无论在哪个平台上，架构都是尽可能高效的。

这是一些表的伪代码模式布局，然后我会解释我想做的事情。

Manufacturers
  ManufacturerID (Identity)
  ManufacturerName
  ManufacturerStreetAddress
  ManufacturerZipCodeID
  ...

ZipCodes
  ZipCodeID (Identity)
  ZipCode
  ZipCodeStateID
  ...

States
  StateID (Identity)
  StateName
  StateAbbreviation
  ...

Cities
  CityID (Identity)
  CityName
  CityStateID
  ...

抱歉，这只是伪模式，因为我正在休息时在纸上设计，但在继续之前有一个问题。我的想法是邮政编码属于一个州和一个城市，但没有一个城市属于任何一个特定的邮政编码，它可能有很多。如果我将邮政编码放入制造商表中，则希望能够获取州和城市。但我不想在其他表中使用太多ID。我的意思是，在ZipCodes和Cities中都有StateID可能会有点多。一个州可以有多个名称相同的城市，多个州也可以有名称相同的城市。但我不确定是否需要一个CityNames表，然后是一个CityStates表（CityNameID和StateID）。我知道有购买位置数据库的地方，也许有些免费的，我可以使用并且不必担心这个。但是，我想了解这方面的内容，因为我相信这将有助于我今后的模式设计，而且如果需要更改，我也希望具有布局的可定制性。

问题：

1.作为伪模式，它是否正确或是否可以更好（意见）？

2.它被称为“合理化”数据库还是其他什么名称（会投票支持正确答案）？到哪里算过头了（意见）

3.还将有一个用户表以及包括地址（团队，首都等）的其他表，因此，如果伪模式在理论上是正确的，则对于这样的数据库，是否是一个好计划（意见）？

谢谢您所有人的时间，我将投票支持任何详尽和条理清晰的答案。首选数据库专家或具有多年数据库经验的人，但我会听取所有答案。另外，我不确定这是否应该是社区维基，但我现在没有标记它。谢谢。

更新：我忘了提到我知道“合理化”数据库需要连接和有时子查询。我通常滥用LEFT OUTER JOIN，但是除了执行4个不同的查询之外，绑定这些表以显示地址的最有效方法是什么？谢谢。

更新：好吧，现在这可能太规范化了，或者规范化程度不够，或者根本没有，但您们能否告诉我是否喜欢这个伪模式更好？

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

也许你们知道更好的编写查询的方法。但不管怎样，这个架构是否比第一个更好呢？

- XstreamINsanity

1

你不能这样做，邮政编码不仅属于一个城市。在农村地区，一个邮编可能包含多个小镇。当然，城市也可能有多个邮编。将整个地址存储在制造商地址表中。 - HLGEM

据我所了解，当我将包裹送到邮局时，他们更关心的是邮政编码而不是城市/镇。这就是为什么我想知道最好的方法来将城市与多个邮政编码以及相反的方式联系起来的原因。我相信这是可行的，也肯定已经有人做过了，我只是想知道如何去实现它。 - XstreamINsanity

另外，我不想存储整个地址的主要原因是我想提供一种简单的方式来搜索特定城市、邮政编码、州等地区的制造商、用户或任何对象。使用ID比解析整个地址要容易得多。除非您指的是整个地址中包含邮政编码、州、城市等列。 - XstreamINsanity

是的，谢谢。当我阅读你们的答案时，我想起在学校听说过“第三范式”。感激不尽。 - XstreamINsanity

邮编是唯一的，将其设为您的邮政编码表的主键。州缩写也是如此，因此我建议您将其用作州表中的主键。 - TMN

只是让你知道，邮政编码可以对应多个城市，一个城市也可以有多个邮政编码——而且一个邮政编码可能跨越多个州。城市->州是一种层次结构，但邮政编码只是地址的属性。 - Adam Musch

3个回答

1

我不是数据库专家，但在我的看法中，给定的伪模式似乎是不正确的。以下是解释。从问题中已知的事实是：

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

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

STATE
---
State ID (PK)
State Name

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

然后，一个逻辑问题出现了。如果我们知道一个Zip ID，我们如何检索City ID？为了回答这个问题，我们需要提供Zip和City之间的链接。这个链接应该放在哪里？它不在City表中，因为从事实＃3中我们知道一个城市可以有许多不同的Zip代码。所以它必须在ZIP表中。这是我们ZIP表的下一个版本：

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

现在，既然我们可以从Zip转移到City，我们将讨论City表。一个城市的名称可能与其他城市同名。因此，我们不需要强制（城市名称字段）唯一。所以这是我们的第一个版本的City表：

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

同样的逻辑问题再次出现。我们如何从城市知道州？必须在这两个表之间创建一个链接。同样地，了解事实＃4不能保证城市名称的唯一性。链接必须放在城市表上。因此，这是我们下一个版本的城市表：

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

通过这个链接，我们可以正确地检索状态。总的来说，我们可以通过城市ID（在邮政编码表中提供）从邮政编码移动到城市，并且我们可以继续通过城市ID（在城市表中提供）从城市移动到州。

从数据库的角度来看，合理化数据库是好的，但从编程的角度来看可能被认为是“邪恶”的。因为它迫使程序员编写越来越多的类。毕竟，“太远”可以定义为“表变得不合理”。城市名称表似乎不合理，因为它是一个属性，而不是实体。如果我的数据库分析师创建了这样一个不合理的表，我会很高兴地标记“太远” :)

另一方面，过度合理化数据库可能会极大地影响数据库性能。根据我的经验，它会使查询运行变慢。

关于其他问题，如用户、团队、首都等，我现在无法发表任何意见，因为我还没有看到这个问题。

- Hendra Jaya

其他表格之间并没有太大的区别。它们也只是拥有地址，但我想像制造商那样将它们的地址与前面提到的表格联系起来。然而，我正在尝试找到一个例子，即如果一个邮政编码被两个州共享会怎样。我知道我的妻子的家人住在AL州的Toney，而Toney就在TN州的边界上，因此他们共享这个城市，但不确定是否共享邮政编码。如果他们共享，则需要为该邮政编码创建两个条目，每个都有一个城市ID。 - XstreamINsanity

我想问大家的问题是，如果查询速度慢了15毫秒，甚至慢了2秒，这是否代价太高？还是更多地取决于用户的需求？我猜你们也需要知道数据库的大小。 - XstreamINsanity

2

设计数据库的一般规则是尽可能进行规范化。然后，如果你发现某些区域会存在性能问题，为了提高性能，可以对这些区域进行反规范化。然而，你不应该根据自己的猜测来进行反规范化，因为你认为某个地方可能会出现问题。你应该测试你的假设，并在测试证明反规范化可以提升性能时进行反规范化。 - wadesworld

谢谢，这也是我所想的。我非常感激大家的帮助。 - XstreamINsanity

1

我对你的设置方式没有太大问题。在邮政编码中使用州标识可能会有危险 - 我不会感到惊讶，如果有一些跨越州界限的邮政编码，但我不确定。

通过将州、城市和邮政编码存储在单独的表中，您将进行许多连接操作，但是如果处理数据库时地址没有一致性措施，那么这比几个连接更加令人噩梦。例如，您最终会得到“NY”、“ny”、“Ny”、“New York”和“NewYork”。因此，我认为为州、城市和邮政编码拥有单独的表在长期运行中会产生回报。

- wadesworld

是的，这正是我所考虑的。当我在等待答案时，我也在思考，但现在已经决定不这样做了，那就是允许用户输入他们的城市、州、邮编等信息，如果这些信息不在数据库中的话。这样它就可以自行构建，我就不必手动完成所有工作了。我可以给自己编辑权限，但同时，这可能需要和我自己手动输入一样长的时间。而且我相信可能有一些邮政编码跨越州界。谢谢你的回答。 - XstreamINsanity

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- JohnK813 · Accepted Answer

我一直听说过“规范化”，但我们谈论的是同一件事。

最简单的方法可能是将城市、州和邮政编码合并到一个表中。你甚至可以考虑使用邮政编码本身作为主键，尽管我能想到两个原因让你要避免这样做：

东北部的州有以0开头的邮政编码，如果你将邮政编码设置为数字字段，它们将被截断。
如果你使用邮政编码作为主键，你就不能在多个城镇中多次使用该邮编。就像你所说的，邮局更关心邮编而不是城镇名称。但这种设置会限制你以后在这些单独的城镇上进行搜索。

要在以后通过城市、州或邮政编码进行搜索，只需将此表与制造商表JOIN即可。你可以使用INNER JOIN - 除非制造商表中的ManufacturerZipCodeID字段为空，那么你需要使用LEFT JOIN来显示它们。