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.
Cat
can be taken to the sameVet
by differentOwners
in distinct points in time. So, (2) is it possible for a particularCat
to be brought to the same specificVet
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