Best practice for person data

Question

Say I am designing a data model for a company with multiple 'actors', at the least employees and customers, but perhaps many more. There's likely some common core for this like name, address, phone/email. Beyond that there is likely different set of fields for each.

So is the common best practice

• keep the (minimum) person details in one table
• make different tables for the different special categories
• FK the second into the first

Or is it just replicate things like name contact into different tables everywhere its needed?

---

I know Postgres has inheritance and this seems to be a classic case for that; but I'd like to assume more generic/standard RDBMS compliance...

Answer 1

I think this is going to come down to preference.

I could see a data model which had a People table, and Customers and Employees tables with FKs to People

CREATE TABLE People (PersonID BIGINT IDENTITY PRIMARY KEY, FirstName NVARCHAR(50) )
CREATE TABLE Employees (EmployeeID BIGINT IDENTITY, PersonID BIGINT References People(PersonID))
CREATE TABLE Customers (CustomerID BIGINT IDENTITY, PersonID BIGINT References People(PersonID))

Throughout the environments I've worked in it has been far more typical to see various types of people split amongst separate tables with similar columns, which do not have a relationship to each other:

CREATE TABLE Employees (EmployeeID BIGINT IDENTITY, FirstName NVARCHAR(50))
CREATE TABLE Customers (CustomerID BIGINT IDENTITY, FirstName NVARCHAR(50))

On a related aside, do yourself (and your successors!) a favor and properly handle Addresses and Phone Numbers:

CREATE TABLE PhoneNumbers (PhoneNumberID BIGINT IDENTITY, PersonID BIGINT, PhoneType TINYINT, IsPrimary BIT...)
CREATE TABLE Addresses (AddressID BIGINT IDENTITY, PersonID BIGINT, AddressType TINYINT, IsPrimary BIT...)

These are not properties of a Person (or Employee, Customer) and should not be kept on those rows.

Quick example of PhoneNumbers:

DECLARE @PhoneNumbers TABLE (PhoneNumberID BIGINT IDENTITY, PersonID BIGINT, IsPrimary BIT, PhoneType TINYINT, PhoneNumber NVARCHAR(20))
DECLARE @PhoneTypes TABLE (PhoneTypeID TINYINT, PhoneTypeName NVARCHAR(10))
INSERT INTO @PhoneTypes (PhoneTypeID, PhoneTypeName) VALUES
(1, 'Home'),(2, 'Cell')
INSERT INTO @PhoneNUmbers (PersonID, IsPrimary, PhoneType, PhoneNumber) VALUES
(1, 1, 1, '01441855472665'),(1, 0, 1, '01441554752665'),
(1, 1, 2, '01441844741165'),(1, 0, 2, '01441554722233')
SELECT *
  FROM @PhoneNumbers p
    INNER JOIN @PhoneTypes pt
      ON p.PhoneType = pt.PhoneTypeID