Database Normalization scenario

Question

I have the following requirements:

1. A Project can be owned by either a company or a person. What would be a good solution to this design problem?

First approach would be to include references to both Person and Company tables in the Project table. But the problems with join would be very difficult to manage.

Second approach, that is used in my workplace is to use EntityType and EntityId where EntityType can be (1=Company or 2=Person) and EntityId refers to the record id in the respective table. I can see that join can still be an issue with this approach as it will need to do a union after join with these tables. As Project is one of my key entities, I can see this being a very expensive routine.

I an interested to find out if there is a better approach to this design issue?

Answer 1

Design a new table OwnerType. This can have rows individual or company. Now include a reference from table OwnerType to ProjectOwner table & include a composite key in your project table.

Answer 2

I would just create an EntityTable with ID surrogate key.
Then have Company and Person tables which have EntityID FK
to this table. You don't really need EntityType column,
you can do inner join from Person to EntityTable; if you find
no row, then it's a Company, not a Person (and vice versa).
But this is subjective, I am not sure if there aren't any
better practices.

Answer 3

From a pure SQL perspective I'd go with the following:

Projects
 - ProjectID
 - OwnerID -> references ProjectOwners.OwnerID

ProjectOwners
 - OwnerID (identity and PK)

Company -> inherits from ProjectOwners
 - CompanyID (PK and FK to ProjectOwners)

People -> inherits from ProjectOwners
 - PersonID (PK and FK to ProjectOwners)

This design allows you to keep full RI. The downside is that there are more tables involved in any joins which will impact performance.