如何正确索引与其他两个表具有一对多关系的表？

Question

想象一下我有三个表，分别称为“客户”、“公司”和“电话号码”。客户和公司都可以拥有多个电话号码。索引phone_numbers 的最佳方法是什么？同时拥有 customer_id 和 company_id 并将其中之一保留为空？如果有两个以上的表与phone_numbers 具有一对多关系怎么办？

Answer 1

您的业​​务规则可能只规定一对多，但实际上人和人之间的关系。公司之间可以是多对多的关系。一个人可以有很多电话号码（家庭电话、手机号码等），而一个电话号码可以与很多人（我自己、我的另一半等）相关。同样，公司号码和我的公司号码可以相同 - 您只需使用分机号码即可直接联系我。

对外键建立索引是一个好主意，但要注意过早的优化。根据设置，我会考虑对电话号码列进行唯一约束，但我不会将电话号码列本身作为主键。

Answer 2

我会在客户和公司表中使用身份列，然后在电话号码表中按照您所说的操作，将一个保留为空，另一个填充。我做了类似的事情，只要您验证数据，这样它就不会在两个值都为空的情况下运行，效果很好。对于更优雅的解决方案，您可以有两列：一列是 id，另一列是类型标识符。假设 1 代表客户，2 代表公司，这样您就不必担心空数据或大量额外列。

Answer 3

我将向phone_numbers 表添加两列。第一个是一个索引，告诉您要与哪个表关联（例如，1 = 客户，2 = 公司）。第二个是相应表的外键。

这样您就可以添加任意数量的电话号码源。

如果某一个人或公司有多个电话号码，则phone_numbers 表中将有多行。

Answer 4

我最接近模式的东西如下——任何两个具有多对多关系的实体都需要它们之间有一个关联实体（交叉引用表），就像这样（假定代理键）：

CREATE TABLE CUSTOMER_XREF_PHONE
( CUSTOMER_ID      NUMBER NOT NULL,
  PHONE_NUMBER_ID  NUMBER NOT NULL,
  CONSTRAINT       CUSTOMER_XREF_PHONE_PK 
    PRIMARY KEY      (CUSTOMER_ID, PHONE_NUMBER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_UK 
    UNIQUE           (PHONE_NUMBER_ID, CUSTOMER_ID),
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK01
    FOREIGN KEY      (CUSTOMER_ID)
      REFERENCES       CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE,
  CONSTRAINT       CUSTOMER_XREF_PHONE_FK02
    FOREIGN_KEY      (PHONE_NUMBER_ID)
      REFERENCES       PHONE_NUMBERS (PHONE_NUMBER_ID) ON DELETE CASCADE
);

这样的实现模式可以：

• 受到数据库级引用完整性约束的完全保护

• 支持双向访问（有时您需要查看还有谁拥有该电话号码）

• 清理

• 通过使用“关系类型”属性来映射实体之间的多个独立关系来进行扩展，
  如：

  • 客户有家庭电话号码
  • 客户有白天电话号码
  • 客户有传真电话号码
  • 客户有手机号码