title: Table-Per-Type Database Design Advice
body: <p>I'm looking for advice for designing a database that has generic entities that want to be related to several different other entity types.  Horrible intro sentence, I know ... so please let me explain by example.</p>
  
  <p>Consider that I have two different entities Employees and Customers with table defs:</p>
  
  <pre><code>Employees
  ----------
  EmployeeID int PK
  FirstName varchar
  LastName varchar
  ... other Employee specific fields
  
  Customers
  ----------
  CustomerID int PK
  FirstName varchar
  LastName varchar
  ... other Customer specific fields
  </code></pre>
  
  <p>A better design might have the common fields, FirstName and LastName, in a related base table, but that's not the part I'm struggling with.</p>
  
  <p>Now, consider that I want to be able to store an unlimited number of Addresses and PhoneNumbers for my Employees and Customers, and define the tables:</p>
  
  <pre><code>Addresses
  ----------
  AddressID int PK
  AddressLine varchar
  City varchar
  State varchar
  PostalCode varchar
  
  PhoneNumbers
  -------------
  PhoneNumberID int PK
  PhoneNumber varchar
  PhoneExtension varchar
  </code></pre>
  
  <p>And then two additional tables to relate Addresses and PhoneNumbers to the Employees:</p>
  
  <pre><code>EmployeeAddresses
  ------------------
  EmployeeAddressID int PK
  EmployeeID int FK Employees.EmployeeID
  AddressID int FK Addresses.AddressID
  EmployeeAddressType enum
  
  EmployeePhoneNumbers
  ---------------------
  EmployeePhoneNumberID int PK
  EmployeeID int FK Employees.EmployeeID
  PhoneNumberID int FK PhoneNumbers.PhoneNumberID
  EmployeePhoneNumberType enum
  </code></pre>
  
  <p>And two similar tables, CustomerAddresses and CustomerPhoneNumbers, to relate Addresses and  PhoneNumbers to the Customers table.  Any Employee-specific or Customer-specific aspects of the Addresses and PhoneNumbers, like EmployeeAddressType in the above, also go in these last four tables.</p>
  
  <p>From what I've found researching the Internet, this design is called Table-Per-Type (TPT) or Table-Per-Subclass (TPS).  And the polymorphic advantages seem appealing, e.g., I could add and AddressLine2 to the Addresses table down the road and both my Employess and Customers automatically gain the benefit of the extra address line.</p>
  
  <p>The disadvantages noted by those sources on TPT are slower query speed and hard to implement.  And now my fairly open-ended plea for advice ...</p>
  
  <p>What other disadvantages am I not considering?  What gotchas can you run into trying to maintain and evolve an application based on this design?  And finally, is the above design what most experienced database designers would use?</p>
  
  <p>Thanks.</p>