sql-server - 将子表关联到多个父表

Question

我正在尝试找出将这些表关联在一起的最佳方法。假设我有下表:

• tblPerson
• tbl组
• tbl资源

每个表中的每一行都可以有多个与之关联的电子邮件地址，因此我需要一个单独的表并将其关联回来。

是否有方法可以使单个表(tblEmail)与每个表相关联。我考虑在每个父表中使用 uniqueidentifier 字段，并将其用作电子邮件表中的键。它将保证是唯一的。我只是无法在电子邮件表中创建 FK 以保持完整性。不过，这是可以管理的。

有什么奇特的方法可以做到这一点吗？我正在 SQL 2008 R2 中创建这些表。

谢谢卡尔

Answer 1

这是SQL的核心部分。在正确的关系设计中，您不会将电子邮件地址与人员、组或资源相关联，而是将人员、组和资源与电子邮件相关联。

因此，电子邮件表为:

CREATE TABLE dbo.tblEmail (
  emailID int IDENTITY PRIMARY KEY,
  email varchar(500)
)

如果每个实体只需要一封电子邮件，则只需在对可能需要电子邮件的内容进行建模的每个其他字段中插入一个 emailID 即可。

ALTER TABLE dbo.tblPerson
ADD emailID int REFERENCES dbo.tblEmail(emailID);

ALTER TABLE dbo.tblGroup
ADD emailID int REFERENCES dbo.tblEmail(emailID);

ALTER TABLE dbo.tblResource
ADD emailID int REFERENCES dbo.tblEmail(emailID);

如果每个实体需要多个电子邮件地址，则需要插入一个附加表，以将电子邮件地址集插入到特定地址。 (除非您有技术原因需要单独处理地址，否则我不会这样做，例如在批量电子邮件系统中，如果有人将同一电子邮件用于自己和组织的用途，您希望避免重复。)

CREATE TABLE dbo.tblEmail (
  emailID int IDENTITY PRIMARY KEY
)

CREATE TABLE dbo.tblEmailAddress (
  eAddrID IDENTITY PRIMARY KEY,
  eAddr varchar(500)
)

CREATE TABLE dbo.tblEmailSet (
  emailID int REFERENCES dbo.tblEmail(emailID),
  eAddrID int REFERENCES dbo.tblEmailAddresses(eAddrID),
)

例如，为了将所有电子邮件的列表返回给名为“Smith”的任何个人、组或资源，您需要运行以下查询:

SELECT DISTINCT A.eAddr
FROM (
  SELECT emailID FROM dbo.tblPerson WHERE Name = 'Smith'
  UNION
  SELECT emailID FROM dbo.tblGroup WHERE Name = 'Smith'
  UNION
  SELECT emailID FROM dbo.tblResource WHERE Name = 'Smith'
) AS PGR
INNER JOIN dbo.tblEmailSet AS S
  ON PGR.emailID = S.emailID
INNER JOIN dbo.tblEmailAddress AS A
  ON S.eAddrID = A.eAddrID

那个丑陋的UNION，顺便说一句，是你真的不想这样做的原因之一，除非你有技术需要来唯一地检索数据。虽然我有时会进行这种多对多对多连接，但在这种特殊情况下，它是一种“代码味道”，并且是一个指示器，而不是跟踪“人员”、“组”和“资源”，您应该使用“类型”指示符来跟踪“联系人”，以判断联系人是个人、组还是资源。

(或者也许您永远不需要获取一堆电子邮件地址，而只需要一个可以检查白名单的电子邮件表...)