Database Design - NULL Foreign Keys_问答_开发者

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. 开发者_StackOverflow中文版 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?

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.

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!

If each puppy really can be owned only by one and only one person, yes of course leave the fk blank/NULL if it's not yet owned.

Otherwise, I suggest 3 tables

puppy info
owner info
puppy-owner

puppy owner rows will have two columns: puppy-id, owner-id. Even though you're saying that a puppy can have only one owner, the fact is that it is likely to be "owned" by all the adults in the household. If it's a show dog, it's likely to be co-owned by the breeder and one or more others.

This is an interesting modelling problem, because it could be argued that the puppy store owns all the puppies which are not owned by anybody else. After all, if Li'l Cujo goes on the rampage and nips the ankles of a few customers the puppy store owner would be liable for the cost of all those tetanus jabs. When Patti Page bought that doggy for her sweetheart the transaction was a change of ownership, not the creation of it.

The logic of this argument is that OwnerId is a NOT NULL column.

The tables you currently have (Puppy and Owner) should be fine. In your Puppy table, you will have a column called something like OwnerID which is a FK to the Owner table. It is fine to allow this to be NULL. When it's null, no one owns the puppy.

Create table owner (ownerid int PRIMARY KEY, ownername varchar(50) not null)

Create table dog(ownerid int, dogid int, dogname varchar(50), CONSTRAINT pk_col PRIMARY KEY (ownerid, dogid), constraint fk_col foreign key (ownerid) references owner(ownerid) );

This is the best solution you can have. What the table design communicates is you have the list of owners in an owner table and table dog only has those entries where the owner exists in the owner table which is the parent table and he has a dog. That's only those puppies who have an owner have any entry into the dog table.

A query to support your requirements.

SELECT owner.ownerid, dog.dogid, dog.dogname FROM owner, dog
WHERE owner.ownerid = dog.ownerid

You may create a special owner "Nobody" and make all unowned puppies refer to it, instead of having a null owner. This may make sense if your database cannot index null FKs and you start to have performance issues looking for unowned puppies.

This complicates design a bit; if in doubt, try the null owner approach first.