Database Design - NULL Foreign Keys

Question

and thanks for reading.

I'm making a DB for a puppy shop. I have a table for puppies and a table for owners. A puppy can have one owner, owners can own more than one puppy, but not all puppies are owned. What's a good way to handle this situation?

• Do I use a FK in the puppy table that is NULL if the puppy doesn't have an owner?
• Do I create an association table that is a one-to-many mapping of owners to puppies and have a flag on the puppies that gets marked if the puppy is un-owned?
• Do I create two tables? One table can be for puppies that are owned and it has a NON-NULL FK to the owner table and another table that holds the puppies that are not owned?

Thanks for the help.

This question is really aiming at, how do I mark a row as global, and allowed to be viewed by any user?

Answer 1

Solution 1) is the correct one. A puppy can have either no owner or a single owner, so the column is either populated with an existing owner or NULL.

Answer 2

I would have the following tables:

Dog
Owner
DogOwner (contains non-nullable DogID and OwnerID FKs that together make up the PK)

Then, you would do:

select *
from Dog d
left outer join DogOwner do on d.DogID = do.DogID
left outer join Owner o on do.OwnerID = o.OwnerID

This query retrieves all dogs, even those with no owner.

This has a few improvements over your design:

• Names the table Dog because dogs don't stay puppies very long (sniff)
• Uses the intersection table DogOwner, because Dogs can have more than one owner. I know mine does!