Database Design - NULL Foreign Keys?

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.

1 because it answers the question, but I concur with others that a dog should be able to be owned by more than one person, or at least owned by the store – MrChrister Feb 1 '10 at 17:20 3 As far as the store is concerned, it seems likely that a puppy has only one owner. Maybe Bob and Sally both think of Fido as "their puppy", but from a store receipt point of view, they sold the puppy to one person. If it's possible for a puppy to be returned and resold and you need to keep a record of this history, that would be a different story.

– Jay Feb 1 '10 at 17:28.

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!

1 assuming DogOwner. DogID and DogOwner. OwnerID are both NOT NULL i.e.

Model missing information using the absence of a row in a relation. – onedaywhen Sep 30 '10 at 13:54.

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.

3 This is a non-optimal solution from a data perspective. The semantic implication of this decision is that all puppies have owners, and you have to know that ID #1 == "Nobody" to be able to exclude it with a WHERE clause. It is preferable that an unowned puppy not have an owner value, because the conclusion that it has no owner is both immediate and explicit.

– Eric Kolb Feb 1 '10 at 17:20 Surely it is. It is just a workaround for a case when the database cannot index nulls well. E.g.In Oracle a row is not included in the index if all indexed columns are null.

If you have a small number of nulls, you have to make a full scan to find them, while for any other value a much faster range scan can be used. There are two typical workarounds: inclusion of an unused non-null column in the index, or adding a dummy "pseudo-null", as I supposed. – 9000 Jun 5 '10 at 1:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.