3

please consider the following scenario. An pet owner may have multiple cats, and may also have multiple dogs. Some of the dogs are related (ie they fight :-) ) with some of the cats of the same owner.

The following relational design does not impose this restriction, as Cats and Dogs of different owners may be related. Is there a way to impose this restriction by relational design?

enter image description here

Markos Fragkakis
  • 7,499
  • 18
  • 65
  • 103
  • 1
    Just add the OwnerId in the relation table between Cat table and Dog table. – Wahid Bitar Sep 27 '12 at 14:42
  • @WahidBitar That alone is not enough - you have to ensure that the junction table matches the **same** owner as the cat and the dog, not just any owner. To do that, owner's PK essentially needs to be propagated down the whole hierarchy. – Branko Dimitrijevic Sep 27 '12 at 17:34

1 Answers1

1

You'd need to use identifying relationships to migrate the owner's PK down both "sides" and to the "bottom" of the diamond-shaped dependency:

enter image description here

Since CatDog.OwnerId is just one field, it cannot identify multiple owners per row, and since it's a FK towards both kinds of animals, this one owner must match both cat's and dog's owner.

In other words, a cat can only relate to a dog from the same owner.

As you can see, cats and dogs are identified differently from what you probably expected. A cat is identified by its owner and differentiated from other cats of the same owner by its CatNo. Ditto for dogs.

If you need a "simpler" key, you can always just add a surrogate key, or alternatively, you could completely eliminate CatNo and DogNo by "abusing" UNIQUE constraint solely for the purpose of migrating the OwnerId:

enter image description here

(The U1 denotes the UNIQUE constraints.)

Now you can identify animals more concisely, but there is a downside: the UNIQUE constraint is completely redundant from the perspective of enforcing uniqueness. It's a super-set of the PK and the PK is enforcing uniqueness on its own just fine. The only purpose of the UNIQUE constraint is to enable CatDog.OwnerId to reference the Cat.OwnerId (and Dog.OwnerId) - most DBMSes require the parent endpoint of a foreign key to be a key.

Some DBMSes (Oracle) will allow you to use only one index to enforce both PK and UNIQUE constraint, but most won't. Every additional index hurts the insert/update/delete performance.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167