Database Design - NULL Foreign Keys
data-modeling, database-design, foreign-keys, sql
Solution
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.
Problem
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?