Database Design - Removing ambiguity without adding loop

Question

I'm looking for a better approach to the problem (and my solution) described below. I've attached an image of my initial design

Let's say I have a cat, and I'd like to find its owners and what vets they took it to. I can easily find out what vets the cat has been to, if I choose a specific vet, I want to know what owners have brought the cat there. I run into problems here, because the list of owners that came to the vet may include owners who own cats other than the one I've found. So now my thought is to say I've got this cat, and I'm at this vet, I want to see all owners who have taken this cat to this vet.

So I add a relationship between the cat and it's owners (this cat is owned by many people, and each of them has multiple cats i.e. many-many). Now I can say that I know these people own this cat, and I know what vets the cat has been to. I can now select all owners who have taken the cat to a specific vet.

I am of course aware that loops are not inherently bad in a database design (as long as no redundancy exists). I'm not convinced that redundancy doesn't exist here, the many-to-many relationship makes me feel that this is the only approach, but I was hoping that someone might have a better approach to this problem.

Answer 1

If I understand your scenario properly, the following assertions are relevant:

• A Cat has one-to-many Owners or, put another way, an Owner has one-to-many Cats.
• A Cat is involved in one-to-many Visits to one-to-many Vets.
• In each Visit to one specific Vet, one Cat is taken by one-to-many Owners.

From this set of assertions I have derived (and uploaded to Dropbox) the following Cats and Vets Data Model, which depicts the way I would approach your specifications.

As you can see in such IDEF1X^[1] data model, I consider important to add an entity that represents the specific event when a Cat (brought by one-to-many CatOwners) visited a particular Vet, and I called it Visit.

And yes, I also think that the CatOwner entity (that you have alredy included in your diagram) is very useful since, in addition to preventing a Person who is not an Owner of a particular Cat from taking such Cat to a Vet, it may also serve the purpose of relating a Cat and one-to-many of its Owners to a specific Visit to an individual Vet.

It is also necessary to add a many-to-many relationship to provide the advantage of associating one-to-many CatOwner ocurrences to one-to-many Visit instances (each Visit received by one particular Vet), and I propose the relationship called CatOwnerVisit to cover this requirement.

Note

_{1. IDEF1X is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the U.S. National Institute of Standards and Technology (NIST).}