主页 / dba / 问题 / 125198
Accepted
Human_AfterAll
Asked: 2016-01-05 17:08:40 +0800 CST

选择 PK：VARCHAR(2) 还是 SMALLINT(2)？

  • 772

今天我们讨论了以下内容：

巴西有 27 个州，每个州都有自己的缩写（就像美国一样）。所以我们有RJ里约热内卢、SP圣保罗、MG米纳斯吉拉斯等地。

我们的一位程序员提议，我们应该使用我们计划添加到新项目的表中的缩写（ RJ、SP、MG等） 。PKStates

推断我们数据库的使用，我反驳了他的论点，说如果我们——有一天——将我们的服务扩展到其他国家，我们将遇到重复缩写的问题，例如：在美国有SC南卡罗来纳州和巴西我们有SC去圣卡塔琳娜的；MT和PA也是如此MA。指望这一点，我们已经同意应该有一个ID专栏 as PK IDENTITY。

现在，假设我们不将服务扩展到其他国家并只留在巴西，我开始考虑使用 VARCHAR(2) 列作为 PK 的想法。在这种情况下，这听起来不像是一个完全糟糕的主意。是吗？为什么？在哪些情况下可以应用？是否应该考虑内存以便从一个到另一个进行选择？

sql-server database-design

3 个回答

  1. Best Answer

    在大多数情况下，我同意@Aaron 的方法，但这恰好是少数几个可以使用真正自然键的实例之一：ISO 代码（特别是ISO 3166）。对于那些不熟悉 ISO 的人，用他们自己的话来说（来自http://www.iso.org/主页）：

    我们是 ISO，国际标准化组织。我们制定和发布国际标准。

    ISO 3166-1描述了国家代码。虽然有几种代码可供选择（2 个字符、3 个字符和数字），但 2 个字符代码是推荐的选择，也是使用最广泛的（包括大多数基于国家/地区的顶级域名）。

    ISO 3166-2描述了每个国家的细分（例如州）。该标准分为基于国家/地区的部分（例如，巴西的ISO 3166-2:BR），代码为 1、2 或 3 个字母数字字符。

    所以，你可以这样做：

    CREATE TABLE dbo.Country
(
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      PRIMARY KEY,
  CountryName VARCHAR(50) NOT NULL
);

CREATE TABLE dbo.CountrySubdvision
(
  CountrySubdvisionCode VARCHAR(3) NOT NULL
                                   COLLATE Latin1_General_100_BIN2,
  CountryCode CHAR(2) NOT NULL
                      COLLATE Latin1_General_100_BIN2
                      CONSTRAINT [FK_CountrySubdvision_Country]
                                 FOREIGN KEY
                                 REFERENCES dbo.Country(CountryCode)
                                 ON UPDATE CASCADE,
  CountrySubdvisionName VARCHAR(50) NOT NULL,
  --  LocalizedSubdvisionName NVARCHAR(50) NOT NULL, -- ??
  CONSTRAINT [PK_CountrySubdvision]
             PRIMARY KEY (CountryCode, CountrySubdvisionCode)
);

    这里的想法是，您可以将CountryCode和CountrySubdvisionCode字段放在任何需要“状态”值的表中，然后 FK 回到dbo.CountrySubdvisionon (CountryCode, CountrySubdvisionCode)。

    虽然这种方法使用 3 - 5 个字节，具体取决于国家/地区/细分市场，不如使用 2 个字节紧凑SMALLINT，但它确实具有在这些相关表中放置人类可读/有意义的值的优势。这可以很容易地减少一些 JOIN 的数量（当你只需要 2 个字符的代码时）并且可以（至少稍微）减少调试某些问题所花费的时间。

    请注意，我_BIN2为两个“代码”字段指定了一个（即二进制）排序规则以帮助提高性能。即使有几个额外的字节，这也应该和比较两个SMALLINT值一样快。唯一的缺点是您需要在添加所有大写代码时保持一致，人们需要记住他们在过滤这些代码时需要使用所有大写。

    另请注意，我将ON UPDATE CASCADE条款添加到 FK ondbo.CountrySubdvision以处理 aCountryCode被 ISO 更改的可能性（极不可能）。同样，在放置了CountryCode和CountrySubdvisionCode字段的表上创建的 FK 也需要设置为ON UPDATE CASCADE. CountryCode这会将对in所做的更改传播到dbo.Country具有两个字段的表。这还将传播CountrySubdvisionCode对这些相关表所做的任何更改。这也不太可能发生（尤其是对于我们的大多数系统将要处理的地方），尽管比对CountryCode.

    更新

    现在，假设我们不将服务扩展到其他国家并只留在巴西，我开始考虑使用 VARCHAR(2) 列作为 PK 的想法。在这种情况下，这听起来不像是一个完全糟糕的主意。

    只是为了说明这一点，如果使用这种方法（即 ISO 代码）来表示“州”/“省”和可能的国家，那么从技术上讲，您确实有能力开始只处理单个国家的“州”。dbo.Country在此配置中，您只需CountryCode从dbo.CountrySubdvision. 然后你只需要CountrySubdvisionCode放在任何相关的表中。

    现在，如果您以后发现需要扩展系统以处理其他国家/地区，您可以在那个时候执行以下步骤（这些步骤都不会更改任何CountrySubdvisionCode相关表中的任何现有值）：

    1. 创建dbo.Country表
    2. 填充dbo.Country表格
    3. 从引用的相关表中删除任何 FKdbo.CountrySubdvision
    4. 放下PKdbo.CountrySubdvision
    5. 将CountryCode字段添加到dbo.CountrySubdvision，使其成为NOT NULL您DEFAULT迄今为止使用的唯一国家/地区的国家/地区代码（DEFAULT如果您愿意，可以在此过程结束时将其删除）
    6. 重新创建 PK on dbo.CountrySubdvisionto be on(CountryCode, CountrySubdvisionCode)
    7. dbo.CountrySubdvision为字段创建 FKCountryCode以引用dbo.Country表
    8. 将该字段添加CountryCode到所有已经具有该CountrySubdvisionCode字段的相关表中，使其成为NOT NULL您DEFAULT迄今为止使用的唯一国家/地区的国家/地区代码（DEFAULT如果您愿意，可以在此过程结束时将其删除）
    9. 在这些相关表上为两个字段重新创建 FK 以引用该dbo.CountrySubdvision表
    10. 从和相关表中删除CountryCode字段的默认约束dbo.CountrySubdvision
    • 8

  2. 我的选择大概是这样的。

    CREATE TABLE dbo.Countries
(
  CountryID tinyint PRIMARY KEY, 
  Name nvarchar(64) NOT NULL UNIQUE
);

CREATE TABLE dbo.States
(
  StateID smallint PRIMARY KEY,
  CountryID tinyint NOT NULL FOREIGN KEY REFERENCES dbo.Countries(CountryID),
  Abbreviation char(2) NOT NULL,
  UNIQUE (CountryID, Abbreviation)
);

    笔记：

    1. CountryID是tinyint因为世界上只有196个国家。
    2. StateID是 asmallint因为有 196 个国家 * 多达 100 个州/省。此列可能会被复合键 ( CountryID/ Abbreviation) 替换，但由于状态可能会在引用此列的子表中使用，代理将简化其他表中的这些引用以及连接，并且通常更好 (这是基于我的经验的普遍性；没有总是）。
    3. Abbreviation是 achar(2)因为varchar这么小的 a 没有多大意义，特别是如果所有缩写都完全是字符的话。您可能会考虑稍微大一点的东西（以及作为单独列的全名）；我不知道世界上所有的约定，但对于某些国家/语言来说，两个字符可能不够用。如果您计划支持任何可能在其州缩写中使用 Unicode 字符的国家/地区，您可能还需要使用nchar（我不知道这是否可能；我对此表示怀疑，但您应该检查一下）。
    4. 为简洁起见，我没有命名我的约束，但作为最佳实践，您应该这样做。

    请注意，单独的 IDENTITY 列并不能真正解决您的问题。当然，您可以SC使用 ID 4SC和 ID 342 来区分，但这些代理并没有真正告诉用户任何信息——您仍然需要另一列来标识国家/地区。

    如果你想把自己画成一个角落，并假设你永远不会超越巴西，那么你的设计就简单得多 - 在这种情况下，我可能会选择char(2)（再次，而不是varchar(2)），因为国家不需要其他列这在子表中将是一个非常小的表示。但实际上我不喜欢把自己画在角落里。如果有任何可以扩展的可能性，您可能会这样做，而以后进行更改将比您想象的更痛苦。

    • 4

  3. 如果您想使用数字，请使用 smallInt(2 Bytes) NOT INT。

    在我看来，你应该有一个小的 INT PK 标识，缩写应该在一个带有 UNIQue 的列中。

    • 0

相关问题