Database Design - Foreign NULL Keys

Question

and thanks for reading.

I am doing a database for a puppy. I have a puppy table and a table for the owners. A puppy can have one owner, owners can own more than one puppy, but not all puppies belong. What is a good way to handle this situation?

• Do I use FK in the puppies table, which is NULL if the puppy has no owner?
• Am I creating an association table that is a one-to-many mapping of owners for puppies and has a flag on the puppies that become marked if the puppy does not own?
• Create two tables? One table can be for puppies that belong, and does it have a NON-NULL FK in the owner table and another table that stores puppies that don't belong?

Thanks for the help.

This question really aims at how I can mark a line as global and allow it to be viewed by any user?

Answer 1

Solution 1) is correct. A puppy cannot have either an owner or a single owner, so the column is either populated by the 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 will 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 that do not have an owner.

You have several improvements over your design:

• Names the Dog table because dogs do not stay puppies for very long (sniff)
• Uses the DogOwner intersection table because dogs can have more than one owner. I know what I have!

Answer 3

If each puppy can really only have one and only one person, yes, of course, leave fk blank / NULL if it does not already belong.

Otherwise I suggest 3 tables

• puppy information
• owner information
• puppy owner

puppy owner lines will have two columns: puppy-id, owner ID. Although you say that a puppy can have only one owner, the fact is that it will probably be “owned” by all adults in the family. If it is a show dog, it is likely to be owned by the co-owner and one or more others.

Answer 4

This is an interesting modeling problem, because it can be argued that the puppy store owns all puppies that do not belong to anyone else. In the end, if Li'l Kujo sets off into a rage and delays the ankles of several clients, the puppy's store owner will be responsible for the cost of all these tetanus strokes. When Patti Page bought this dog for her lover, the deal was a change of ownership, not its creation.

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

Answer 5

The tables you have (Puppy and Owner) should be in order. In your Puppy table, you will have a column named OwnerID, which is the FK in the Owner table. It is normal for this to be NULL. When it is zero, no one owns a puppy.

Answer 6

 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 get. What links the design of the table is that you have a list of owners in the owner table, and the table has only those records where the owner exists in the owner table, which is the parent table, and he has a dog. That only those puppies that have an owner have any entrance to the dog table.

Request to support your requirements.

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

Answer 7

You can create a special owner "Nobody" and make all inanimate puppies a link to it, instead of having a zero owner. This might make sense if your database cannot index zero FKs and you have performance issues looking for broken puppies.

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