One to One Relationships

After many weeks, I am back with a question for you all. How do you feel is the best way to handle a one-to-zero or one relationship in a database? Let me explain what I mean.

First, I feel that there are two scenarios where a one-to-zero or one relationship applies. The first example represents two distinct objects, such as an employee and a computer, where a business rule states that one employee cannot have more than one computer, and one computer can only belong to one employee. I would probably argue that in time, this will turn out to be a false business rule, as when one employee leaves, the computer is assigned to another employee. Or when the computer dies, the employee gets another one. But for now, lets just pretend that the one-to-zero or one applies here. I think it makes sense for there to be an employee table and a computer table. Each table would have its own primary key, and lets just assume that they key is an auto-incremented identity. How should the tables be related? Should we add an EmployeeID field to the Computer table? Or should we add a ComputerID field to the Employee table? Here, it really shouldn't matter. We might choose to put the ComputerID in the Employee table because we feel that "employee has a computer" makes more sense than "computer has employee." Either way, it shouldn't really matter, though.

But how about in the second situation where we have a one-to-zero or one relationship to represent a supertype/subtype relationship? For example, we have a supertype of Individual with subtypes of Employee, Contact, and Owner. The Individual table has fields for the first name, middle name, last name, address, phone number, SSN, and there is an auto-incremented identity column which holds the IndividualID. Let's assume that the Employee table has fields for the employee number, title, related department, hire date, start date, and end date. The Contact and Owner table also have unique field in them. So how do we relate the Employee table to the Individual table? How do we represent the "Employee is an Individual" relationship in the database?

I have seen this handled in two ways. First, we could have the primary key of the employee table be the IndividualID. This is also a foreign key to the Individual table. So the IndividualID would exist as the primary key of four tables, the Individual table, the Employee table, the Contact table, and the Owner table. In the Individual table, Employee table, and Contact table, the IndividualID would also be a foreign key to the Individual table.

Or, we could have the primary key of the Employee table be an auto-incremented identity field called EmployeeID. We could then store this ID as a foreign key in the Individual table. So the Individual table would end up with three foreign keys, the EmployeeID, ContactID, and OwnerID. Each of these fields would allow null, and only one field should contain a non-null value.

Which solution is the better one?