2

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

enter image description here

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.

enter image description here

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.

  • Can someone visit the doctor with a cat they don't own? – ypercubeᵀᴹ Jul 15 '15 at 15:04
  • Lets say no. I see what you are getting at, it shows information I can get that I didn't originally expect. My main goal is if I have a cat, I can get a vet it was at, and once I have the vet, I can find the owner of the cat, and that is it. – Sunde Jul 15 '15 at 15:23
  • Good question. (1) By “vet” you mean the veterinarian’s office, right? I assume that a specific Cat can be taken to the same Vet by different Owners in distinct points in time. So, (2) is it possible for a particular Cat to be brought to the same specific Vet by multiple (i.e., one-to-many) Owners concurrently, that is, at the same time? (3) Could you expand on the loop issue? – MDCCL Jul 15 '15 at 18:43
  • Yes, I did mean veterinarian's office, and a cat can be brought to a vet by multiple owners concurrently. – Sunde Jul 15 '15 at 18:55
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.