Creating a single table for two different entities

Question

What is an appropriate way to structure the database table for the following scenario?

I'm trying to model an incident report, part of it will be recording whether (as part of the incident) an employee or customer had to receive medical attention. The same details are to be recorded for both. In the paper version of the form and in the interface, it is shown as a single table. The table headings would be:

Person (either employee or customer)
Injured
First aid administered
Hospitalised
Details

We already have separate employee and customer tables which old their personal details. The new table would include the ID, details on whether the person was injured, received first aid, hospitalised and any other additional details.

So I've been pondering on a few possibilities of how this could be structured:

Two separate tables for employee and customer incidents. The two tables will hold mostly the same fields with the exception of the foreign keys for EmployeeID or CustomerID.
A single table that includes all the incident related fields and additional fields for EmployeeID and CustomerID where these are nullable foreign keys to the primary keys of the Employee and Customer table respectively. A constraint to ensure that EmployeeID XOR CustomerID is not null.
Create 3 new tables: one to just hold the incidents, one to link customer ID with an incident and another to link employee ID with an incident - is this too normalised? (assuming I will never have more than customers or employees to record incident details for)
A single table that includes all the incident related fields with a single field to store either the employee ID or customer ID and a type field to specify whether it's employee or customer.

I'm leaning towards option 1, it seems to be the cleanest and should different fields need to be recorded for customers and employees, it's easy to update a single table without affecting the other. I've discounted option 4, I think this is bad design since I'm not able to add a FK constraint to the ID column.

Are there any reasons why I shouldn't go for option 1 or are there better alternatives I should also consider?

granadaCoder · Answer 1 · 2017-11-08T19:36:34.537

I would create "link" tables. And not repeat the DDL for the Incident.

then you have the ability to create a view which would bring together customers and employees with the incident data.

I agree with the previous answer to separate the incident details.

-- START TSQL

SET NOCOUNT ON


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'vwIncidents' and TABLE_TYPE = N'VIEW' ) 
BEGIN 
DROP VIEW [dbo].[vwIncidents] 
END 
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'CustomerToIncidentLink' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[CustomerToIncidentLink] 
END 
GO


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'EmployeeToIncidentLink' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[EmployeeToIncidentLink] 
END 
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Incident' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Incident] 
END 
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Employee' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Employee] 
END 


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Customer' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN DROP TABLE [dbo].[Customer] 
END 




CREATE TABLE [dbo].[Employee] ( 

    [EmployeeUUID] [uniqueidentifier] NOT NULL,
    [TheVersionProperty] [timestamp] NOT NULL,
    [SSN] [nvarchar](11) NOT NULL,
    [LastName] [varchar](64) NOT NULL,
    [FirstName] [varchar](64) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [HireDate] [datetime] NOT NULL
    )

GO

ALTER TABLE dbo.Employee ADD CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (EmployeeUUID) 
GO


ALTER TABLE [dbo].[Employee] ADD CONSTRAINT CK_SSN_Unique UNIQUE (SSN) 
GO




CREATE TABLE [dbo].[Customer] ( 

    [CustomerUUID] [uniqueidentifier] NOT NULL,
    [TheVersionProperty] [timestamp] NOT NULL,
    [CustomerNumber] [nvarchar](11) NOT NULL,
    [LastName] [varchar](64) NOT NULL,
    [FirstName] [varchar](64) NOT NULL,
    [CreateDate] [datetime] NOT NULL
    )

GO

ALTER TABLE dbo.Customer ADD CONSTRAINT PK_Customer PRIMARY KEY NONCLUSTERED (CustomerUUID) 
GO


ALTER TABLE [dbo].[Customer] ADD CONSTRAINT CK_CustomerNumber_Unique UNIQUE (CustomerNumber) 
GO






IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Incident]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN DROP TABLE [dbo].[Incident] 
END 
GO

CREATE TABLE [dbo].[Incident] 
( 
  IncidentUUID [UNIQUEIDENTIFIER] NOT NULL DEFAULT NEWSEQUENTIALID() 
, IncidentName varchar(24) not null 
, CreateDate smalldatetime not null
)

GO

ALTER TABLE dbo.Incident ADD CONSTRAINT PK_Incident PRIMARY KEY NONCLUSTERED (IncidentUUID) 
GO

ALTER TABLE [dbo].[Incident] ADD CONSTRAINT CK_IncidentName_Unique UNIQUE (IncidentName) 
GO




IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[EmployeeToIncidentLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN DROP TABLE [dbo].[EmployeeToIncidentLink] 
END 
GO

CREATE TABLE [dbo].[EmployeeToIncidentLink] ( 
    [LinkSurrogateUUID] [uniqueidentifier] NOT NULL,
    [TheEmployeeUUID] [uniqueidentifier] NOT NULL,
    [TheIncidentUUID] [uniqueidentifier] NOT NULL
)


GO

ALTER TABLE dbo.EmployeeToIncidentLink ADD CONSTRAINT PK_EmployeeToIncidentLink PRIMARY KEY NONCLUSTERED (LinkSurrogateUUID) 
GO

ALTER TABLE [dbo].[EmployeeToIncidentLink] ADD CONSTRAINT FK_EmployeeToIncidentLinkToEmployee FOREIGN KEY (TheEmployeeUUID) REFERENCES dbo.Employee (EmployeeUUID) 
GO

ALTER TABLE [dbo].[EmployeeToIncidentLink] ADD CONSTRAINT FK_EmployeeToIncidentLinkToIncident FOREIGN KEY (TheIncidentUUID) REFERENCES dbo.Incident (IncidentUUID) 
GO


ALTER TABLE [dbo].[EmployeeToIncidentLink] ADD CONSTRAINT CONST_UNIQUE_EmpUUID_InciUUID UNIQUE (TheEmployeeUUID , TheIncidentUUID) 
GO




IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[CustomerToIncidentLink]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
BEGIN DROP TABLE [dbo].[CustomerToIncidentLink] 
END 
GO

CREATE TABLE [dbo].[CustomerToIncidentLink] ( 
    [LinkSurrogateUUID] [uniqueidentifier] NOT NULL,
    [TheCustomerUUID] [uniqueidentifier] NOT NULL,
    [TheIncidentUUID] [uniqueidentifier] NOT NULL
)


GO

ALTER TABLE dbo.CustomerToIncidentLink ADD CONSTRAINT PK_CustomerToIncidentLink PRIMARY KEY NONCLUSTERED (LinkSurrogateUUID) 
GO

ALTER TABLE [dbo].[CustomerToIncidentLink] ADD CONSTRAINT FK_CustomerToIncidentLinkToCustomer FOREIGN KEY (TheCustomerUUID) REFERENCES dbo.Customer (CustomerUUID) 
GO

ALTER TABLE [dbo].[CustomerToIncidentLink] ADD CONSTRAINT FK_CustomerToIncidentLinkToIncident FOREIGN KEY (TheIncidentUUID) REFERENCES dbo.Incident (IncidentUUID) 
GO

ALTER TABLE [dbo].[CustomerToIncidentLink] ADD CONSTRAINT CONST_UNIQUE_CustomerUUID_InciUUID UNIQUE (TheCustomerUUID , TheIncidentUUID) 
GO



CREATE VIEW dbo.vwIncidents  
AS  
/* combine what is in common with employees and customers for this view */
select e.LastName, e.FirstName, inc.IncidentName
from dbo.Employee e
join
dbo.EmployeeToIncidentLink link on e.EmployeeUUID = link.TheEmployeeUUID
join
dbo.Incident inc on inc.IncidentUUID = link.TheIncidentUUID
UNION ALL
select c.LastName, c.FirstName, inc.IncidentName
from dbo.Customer c
join
dbo.CustomerToIncidentLink link on c.CustomerUUID = link.TheCustomerUUID
join
dbo.Incident inc on inc.IncidentUUID = link.TheIncidentUUID



GO

There is another option (I think?)

Create a Person table, (this has info in common with customers and employees) and then create "sub class" tables for customer and employee that has unique to that entity data.

That's a topic unto itself.

See

How do we implement an IS-A Relationship?

"IS A" or "how to subclass in a database" would be search tips.

I have this example that is close to yours, that does a "subclassing"

-- START TSQL

SET NOCOUNT ON

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'vwPersonEmail' and TABLE_TYPE = N'VIEW' ) 
BEGIN 
DROP VIEW [dbo].[vwPersonEmail] 
END 
GO



IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'PersonEmail' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[PersonEmail] 
END 
GO


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Customer' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Customer] 
END 
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Employee' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Employee] 
END 
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'PersonSuperType' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[PersonSuperType] 
END 
GO


IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Department' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Department] 
END 
GO



CREATE TABLE [dbo].[Department](
    [DepartmentUUID] [uniqueidentifier] NOT NULL,
    [TheVersionProperty] [timestamp] NOT NULL,
    [DepartmentName] [nvarchar](80) NULL,
    [CreateDate] [datetime] NOT NULL,
    [MyTinyInt] tinyint not null
    )


ALTER TABLE dbo.[Department] ADD CONSTRAINT PK_Department PRIMARY KEY NONCLUSTERED ([DepartmentUUID]) 
GO

ALTER TABLE [dbo].[Department] ADD CONSTRAINT CK_DepartmentName_Unique UNIQUE ([DepartmentName]) 
GO




CREATE TABLE [dbo].[PersonSuperType] (
    [PersonSuperTypeUUID] [uniqueidentifier] not null default NEWSEQUENTIALID(),
    [LastName] [varchar](64) NOT NULL,
    [FirstName] [varchar](64) NOT NULL  
)

GO

ALTER TABLE dbo.PersonSuperType ADD CONSTRAINT PK_PersonSuperTypeUUID PRIMARY KEY CLUSTERED (PersonSuperTypeUUID)
GO


CREATE TABLE [dbo].[Employee] ( 

    [EmployeeUUID] [uniqueidentifier] NOT NULL,
    [ParentDepartmentUUID] [uniqueidentifier] NOT NULL,
    [TheVersionProperty] [timestamp] NOT NULL,
    [SSN] [nvarchar](11) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [HireDate] [datetime] NOT NULL
    )

GO

ALTER TABLE dbo.Employee ADD CONSTRAINT PK_Employee PRIMARY KEY NONCLUSTERED (EmployeeUUID) 
GO


ALTER TABLE [dbo].[Employee] ADD CONSTRAINT CK_SSN_Unique UNIQUE (SSN) 
GO

ALTER TABLE [dbo].[Employee] ADD CONSTRAINT FK_EmployeeToDepartment FOREIGN KEY (ParentDepartmentUUID) REFERENCES dbo.Department (DepartmentUUID) 
GO

ALTER TABLE [dbo].[Employee] ADD CONSTRAINT FK_EmployeeToPersonSuperType FOREIGN KEY (EmployeeUUID) REFERENCES dbo.PersonSuperType (PersonSuperTypeUUID) 
GO



CREATE TABLE [dbo].[Customer] ( 

    [CustomerUUID] [uniqueidentifier] NOT NULL,
    [TheVersionProperty] [timestamp] NOT NULL,
    [CustomerNumber] [nvarchar](11) NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    )

GO

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT PK_Customer PRIMARY KEY NONCLUSTERED (CustomerUUID) 
GO

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT CK_Customer_CustomerNumber_Unique UNIQUE (CustomerNumber) 
GO

ALTER TABLE [dbo].[Customer] ADD CONSTRAINT FK_CustomerToPersonSuperType FOREIGN KEY (CustomerUUID) REFERENCES dbo.PersonSuperType (PersonSuperTypeUUID) 
GO




CREATE TABLE [dbo].[PersonEmail] ( 

    [PersonEmailUUID] [uniqueidentifier] NOT NULL,
    [PersonSuperTypeUUID] [uniqueidentifier] NOT NULL,
    [EmailAddress] [varchar](256) NOT NULL
    )

GO

ALTER TABLE [dbo].[PersonEmail] ADD CONSTRAINT PK_PersonEmail PRIMARY KEY NONCLUSTERED (PersonEmailUUID) 
GO

ALTER TABLE [dbo].[PersonEmail] ADD CONSTRAINT CK_PersonEmail_EmailAddress_Unique UNIQUE (EmailAddress) 
GO

ALTER TABLE [dbo].[PersonEmail] ADD CONSTRAINT FK_PersonEmailToPersonSuperType FOREIGN KEY (PersonSuperTypeUUID) REFERENCES dbo.PersonSuperType (PersonSuperTypeUUID) 
GO


CREATE VIEW dbo.vwPersonEmail  
AS  
/* combine what is in common with employees and customers for this view */
select per.LastName, per.FirstName, [UniqueIdentifier] = emp.SSN, pemail.EmailAddress
from dbo.PersonSuperType per
join
dbo.Employee emp on per.PersonSuperTypeUUID = emp.EmployeeUUID
join
dbo.PersonEmail pemail on per.PersonSuperTypeUUID = pemail.PersonSuperTypeUUID
UNION ALL
select per.LastName, per.FirstName, [UniqueIdentifier] = cust.CustomerNumber, pemail.EmailAddress
from dbo.PersonSuperType per
join
dbo.Customer cust on per.PersonSuperTypeUUID = cust.CustomerUUID
join
dbo.PersonEmail pemail on per.PersonSuperTypeUUID = pemail.PersonSuperTypeUUID



GO

So now that I think about it, I might lean toward the "subclassing" way..since your two types of people might have alot of common attributes, and a few distinct ones.

Thanks for the detailed response. Question regarding your first example: Can I prevent the same IncidentUUID being used for an employee and customer? I.e. IncidentUUID must only be used once by either the employee or customer link tables. the The incident details can include sensitive information about the employee/customer so we would not want this to go wrong where a person's details have been added for another person. [particularly since this data has to be added manually with insert scripts occasionally] — 201403540, Nov 10 '17 at 15:42
Make IncidentUUID the PK for the Incident table (to avoid repeats). If you have a strict requirement that the same value cannot be used in the ~link~ tables, you'll have to "subclass" incident to be CustomerIncident and EmployeeIncident that has its own PK, and a FK back to Incident.IncidentUUID. (CustomerIncident.CustomerIncidentUuid PK and CustomerIncident.IncidentUuid (FK)) (repeat for employee) And then the link tables will FK to CustomerIncident.CustomerIncidentUuid or EmployeeIncident.EmployeeIncidentUuid . I wouldn't do that..but if it is a strict requirement. — granadaCoder, Nov 10 '17 at 18:25
I mean, you could argue if you get the FK value wrong for an employee, then one employee could see another employees data. So I don't see any diffierence between an employee and customer. At some point, having the correct FK has to be common sense about the data. — granadaCoder, Nov 10 '17 at 18:26
Remember, a RDBMS (relational database management system, of which sql server is one) is about Relational Integrity. NOT business rules. — granadaCoder, Nov 10 '17 at 18:27

score 0 · Answer 2 · answered Nov 08 '17 at 18:15

0

If you want only to show information about 2 diferent tables in only one, you could use a View. Here you have an example check it out.

https://www.w3schools.com/sql/sql_view.asp => tutorial

https://www.ibm.com/support/knowledgecenter/en/ssw_i5_54/rzatc/rzatcviewmultsql.htm => example

answered Nov 08 '17 at 18:15

Luis Manrique

308
3
15

That's not quite what I'm asking. Yes, I could use a view once I have the tables in place but my question is on the step before this of how these tables should be structured. – 201403540 Nov 08 '17 at 18:19

score 0 · Answer 3 · answered Nov 08 '17 at 18:23

I'd formalize the distinction between the incident and the details.

INCIDENT_HEADER
   Customer ID <nullable, fk into the customer table>
   Employee ID <nullable, fk into the employee table, which already exsits>
   Summary
   Date
   Status (?)

INCIDENT_DETAIL
   DETAIL_TYPE_ID <fk into a type table, it's up next...>
   DESCRIPTION

DETAIL_TYPE
   ID
   DESCRIPTION

DETAIL_TYPE will end up with things like "Injury", "Hospitalized", and "First Aid". You can add more to that table later without touching the details table.

This design lets you add more kinds of details without altering the header table.

Creating a single table for two different entities

3 Answers3