Should I make a foreign key that can be null or make a new table?

Question

I have a small question concerning with how I should design my database. I have a table dogs for an animal shelter and I have a table owners. In the table dogs all dogs that are and once were in the shelter are being put. Now I want to make a relation between the table dogs and the table owners.

The problem is, in this example not all dogs have an owner, and since an owner can have more than one dog, a possible foreign key should be put in the table dogs (a dog can't have more than one owner, at least not in the administration of the shelter). But if I do that, some dogs (the ones in the shelter) will have null as a foreign key. Reading some other topics taught me that that is allowed. (Or I might have read some wrong topics)

However, another possibility is putting a table in between the two tables - 'dogswithowners' for example - and put the primary key of both tables in there if a dog has an owner.

Now my question is (as you might have guessed) what the best method is of these two and why?

Answer 1

The only solution that is in keeping with the principles of the Relational Model is the extra table.

Moreover, it's hard to imagine how you are going to find any hardware that is so slow that the difference in performance when you start querying, is going to be noticeable. After all, it's not a mission-critical tens-of-thousands-of-transactions-per-second appliation, is it ?

Answer 2

I agree with Philip and Erwin that the soundest and most flexible design is to create a new table.

One further issue with the null-based approach is that different software products disagree over how SQL's nullable foreign keys work. Even many IT professionals don't understand them properly so the general user is even less likely to understand it.