0

I am designing a SQL Server database with multiple entities, such as people, families, companies, schools, etc, each with a wide variation in columns.

I'm struggling with the fact that many of these entities need identical child tables, such as email addresses and phone numbers. I do not want to put email address and phone fields directly into the entity tables for several reasons.

For example, I do not know how many email addresses a given person has, and thousands of the persons in the table need none at all. I also do not want an email address to be available to more than one entity.

I came up with the following scenario, where there is a master entity table of only one GUID column. Each type of entity table has a foreign key to that column, as does the EmailAddress table. Each entity row can then be linked to one or more EmailAddress rows with the same EntityID.

The only catch is that the Entity table is the parent of all the other tables. Whenever I add a new row to the Person (or Family or Company) table, I would have to add a trigger or code that writes a GUID to a new row in the Entity table and copies it to the EntityID of the inserted row.

Is there a better way of handling this?

CREATE TABLE dbo.Entity
(
    EntityID UNIQUEIDENTIFIER UNIQUE
)

CREATE TABLE dbo.Person
(
    PersonID UNIQUEIDENTIFIER PRIMARY KEY,
    PersonName VARCHAR(50) NOT NULL,
    EntityID UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (EntityID) REFERENCES Entity(EntityID)
) 

CREATE TABLE dbo.Family
(
    FamilyID UNIQUEIDENTIFIER PRIMARY KEY,
    FamilyName VARCHAR(50) NOT NULL,
    EntityID UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (EntityID) REFERENCES Entity(EntityID)
)

CREATE TABLE dbo.Company
(
    CompanyID UNIQUEIDENTIFIER PRIMARY KEY,
    CompanyName VARCHAR(50) NOT NULL,
    EntityID UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (EntityID) REFERENCES Entity(EntityID)
)

CREATE TABLE dbo.EmailAddresses
(
    EmailAddressID UNIQUEIDENTIFIER PRIMARY KEY,
    EmailAddress VARCHAR(50) NOT NULL,
    EntityID UNIQUEIDENTIFIER NOT NULL,
    FOREIGN KEY (EntityID) REFERENCES Entity(EntityID)
)

I also thought of skipping the Entity table entirely and simply using an EntityID in the EmailAddress table only. I would lose the benefit of a foreign key relationship and have to trust on query joins only.

CREATE TABLE dbo.Person
(
    PersonID UNIQUEIDENTIFIER PRIMARY KEY,
    PersonName VARCHAR(50) NOT NULL
) 

CREATE TABLE dbo.Family
(
    FamilyID UNIQUEIDENTIFIER PRIMARY KEY,
    FamilyName VARCHAR(50) NOT NULL
)

CREATE TABLE dbo.Company
(
    CompanyID UNIQUEIDENTIFIER PRIMARY KEY,
    CompanyName VARCHAR(50) NOT NULL
)

CREATE TABLE dbo.EmailAddresses
(
    EmailAddressID UNIQUEIDENTIFIER PRIMARY KEY,
    EmailAddress VARCHAR(50) NOT NULL,
    EntityID UNIQUEIDENTIFIER NOT NULL
)


SELECT
    Person.PersonID, Person.PersonName, EmailAddresses.EmailAddress
FROM
    Person
INNER JOIN
    EmailAddress ON Person.PersonID = EmailAddress.EntityID
etc...
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
A. Troyer
  • 3
  • 2
  • I think you're actually going a little over the top here with the "normalisation". A Company has an email address, and a person has an email address, so both the `Company` and `Person` tables can have an `EmailAddress` column. If you want the functionality to store multiple Email Addresses for a Person/Company then have a `PersonEmail` and `CompanyEmail` table; not a generic one. Then you can build the foreign key relationships (which you won't be able to do with a single Email table for "everything"). – Thom A Nov 22 '19 at 16:38
  • I actually have six kinds of entities at this point (persons, families, companies, schools, churches, and places) and was getting weary of trying to make identical child tables of both email addresses and phone numbers, not to mention possible future url tables, social media usernames, etc.... – A. Troyer Nov 22 '19 at 16:46

0 Answers0