CODEWITHSUNDEEP
sqldatabase-designforeign-keysdata-modeling
davidemm
davidemm

Reputation: 2009

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. What's a good way to handle this situation?

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?

Upvotes: 4

Views: 1249

Answers (7)

Chaitanya
Chaitanya

Reputation: 1708

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

Upvotes: -1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171381

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!

Upvotes: 5

9000
9000

Reputation: 40884

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.

Upvotes: -3

APC
APC

Reputation: 146209

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.

Upvotes: 2

Liz Albin
Liz Albin

Reputation: 1489

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.

Upvotes: 3

Otávio Décio
Otávio Décio

Reputation: 74250

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.

Upvotes: 11

Randy Minder
Randy Minder

Reputation: 48402

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.

Upvotes: 0

Related Questions