database-design - 协助使用数据库架构(独立于平台)

Question

我有一个意见问题，但同时也有一个可能有正确答案的问题。我正在尝试开发一套产品，并希望确保因为我是自己做的，所以我第一次就做对了。我多次重新编写了架构，每次都认为它更好。然后我可能会遇到一些新想法，它要么需要对模式进行大量工作，要么会破坏我的模式。

在大学里，我学会了“合理化”(我认为这是他们使用的词，可能有点离题)一个数据库，有 5 个级别。据我所知，3级是最常见的。我知道这种做法是为了确保数据不会重复，为此，您必须将表分解为更小的表。并且取决于您将其分解的程度，级别越高。好吧，我不知道我是否想要最高级别，但我知道我希望它尽可能高效。我已经使用了 4 年的 SQL Server 2000/2005/2008 和 2 年的 Oracle，使用 Informix 大约 6 个月(5 年多以前)，使用 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。我的意思是在邮政编码和城市中拥有 StateID 可能是太多次了。一个州可以有多个同名的城市，多个州可以有同名的城市。但我不确定我是否想要一个 CityNames 表，然后是一个 CityStates 表(CityNameID 和 StateID)。我很清楚那里有可供购买的位置数据库，也许有些是免费的，我可以使用它们而不必担心这一点。但是，我想进一步了解这一点，因为我相信这将有助于我将来明智地进行模式设计，而且还因为如果需要更改任何内容，我希望布局的可定制性。

问题:

那个伪模式看起来是正确的还是会更好(意见)？

它被称为“合理化”数据库，还是其他东西(将投票支持正确答案)？以及多远太远(意见)

还将有一个用户表和其他包含地址(团队、国会大厦等)的表，那么如果理论上正确，伪模式是否是这样的数据库的好计划(意见)？

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

更新:另外，我忘了提到我知道“合理化”数据库需要连接，有时还需要子查询。我通常滥用 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

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

Answer 1

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

最简单的方法可能是将城市、州和 zip 合并到一张表中。您甚至可以考虑使用邮政编码本身作为关键，尽管我可以想到您想要避免这种情况的两个原因:

东北各州有邮政编码
以 0 开头，这将是
如果您将邮政编码设为 a，则会被截断
数值字段。

如果您使用邮政编码作为 key ，则不能将该邮政编码设置为多个
多个城镇的时间。喜欢你
说，邮局更关心
关于zip比城镇名称。
但是这个设置会限制你
从搜索那些个人
城镇以后。

稍后要按城市、州或 zip 搜索，只需将此表加入制造商表即可。您可以使用 INNER JOIN - 除非制造商表中的某些字段 ManufacturerZipCodeID 为空，在这种情况下，您需要使用 LEFT JOIN 来显示这些字段。