w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
Relating a child table to multiple parent tables

This is a core part of SQL. In a proper relational design, you don't relate email addresess to perosns, groups, or resources -- you relate the persons, groups, and resources TO the email.

So, with an email table of:

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

If you only need one email per entity, you would just insert an emailID on each of the other fields that model something that may need an email.

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);

If you need multiple email addresses per entity, you need to insert an additional table, to interpolate the set of email addresses to a particular address. (I wouldn't do this unless you have a technical reason to handle the addresses individually, such as a bulk-email system where you want to avoid duplicates if someone uses the same email for their own use and their organization's use.)

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),
)

In order to, say, return a list of all emails to any Person, Group, or Resource named "Smith", you'd run the query below:

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

That ugly UNION, btw, is one of the reasons why you really don't want to do this unless you have a technical need to retrieve the data uniquely. While I've done this sort of many-to-many-to-many join on occasion, in this particular instance it's kind of a "code smell" and an indicator that instead of tracking "People", "Groups", and "Resources", you should be tracking "Contacts" with a "type" indicator to tell if a contact is a Person, a Group, or a Resource.

(Or maybe you never need to grab a bunch of email addresses, and just want a single table of emails you can check for whitelisting...)





© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.