CODEWITHSUNDEEP
sqldatabasedatabase-design
splouie
splouie

Reputation: 23

Table-Per-Type Database Design Advice

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.

Consider that I have two different entities Employees and Customers with table defs:

Employees
----------
EmployeeID int PK
FirstName varchar
LastName varchar
... other Employee specific fields

Customers
----------
CustomerID int PK
FirstName varchar
LastName varchar
... other Customer specific fields

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.

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:

Addresses
----------
AddressID int PK
AddressLine varchar
City varchar
State varchar
PostalCode varchar

PhoneNumbers
-------------
PhoneNumberID int PK
PhoneNumber varchar
PhoneExtension varchar

And then two additional tables to relate Addresses and PhoneNumbers to the Employees:

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

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.

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.

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 ...

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?

Thanks.

Upvotes: 2

Views: 1752

Answers (4)

Neil McGuigan
Neil McGuigan

Reputation: 48287

  1. use single table inheritance to start. it is the simplest, easiest, and fastest.

  2. use the Party Model. Individuals and Organizations are both Parties, and can play the role of customers or employees.

  3. consider email addresses, phone numbers, web sites, and mailing addresses all to be sub-types of "contact method" or address.

  4. if you use a tool like JBoss Hibernate (java) or NHibernate (.net), then this does most of the work for you.

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18950

Employees and Customers are both subclasses of the class People, as noted in the previous response. These two subclasses might not be mutually exclusive.

There is a technique, called Class Table Inheritance. In this technique, there will be three tables, People, Employees, and Customers. Attributes common to all people, like Address, will be in the People table.

You can get the details by visiting this tag and looking under the "Info" tag.

Upvotes: 0

Aheho
Aheho

Reputation: 12821

One disadvantage in your current database design is your database will not prevent an employee from having 2 or more home addresses. It also wont prevent a customer from having NO address for that matter.

You can protect against creating multiple home addresses by changing to a compound Primary Key on the EmployeeAddresses table (PK = EmployeeID, EmployeeAddressType). However if you are using an ORM, many of them only play nice when the PK is one column.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96650

You are far better off starting with a People table and then having a customers table, an employees table etc. The email address, addresses and phone numbers would then relate to the people table not customer or some other specialized table.

The problem with one address table related to multple parent tables is that you cannot set up proper foreign key constraints and will invaraibly end up with bad data.

You can create foreign keys properly that with separate tables but then querying becomes harder (Suppose you need to know everyone in CA), you get duplicate records for people who end up in more than one category (Employees might also be customers) and it is harder to make sure the tables are all updated when the need to change the table structure happens.

Upvotes: 0

Related Questions