Relational design scenario: restrict children rela

Question

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?

Answer 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:

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:

(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.