In an application I'm working on, we have a kind of "restricted one-to-many" relationship. I'm wondering if this has a name, and whether it's possible to enforce at the database level.
A standard one-to-many might be
pets. One person can have many pets, and each pet is of a specified species. You can have 0 pets, or 2 dogs and a cat, or just a parakeet, etc.
In our case, we want to say something like "One person can have many pets, but no more than one of each type." You can have 0 pets, or just a cat, or a dog and a parakeet, but you can never have 2 of any species.
I can only see two ways to do this:
- Have a column on
peoplefor each possible pet's foreign key - a
dog_id, etc. This would mean a lot of
NULLs on the table, though.
- Make a standard one-to-many, where each row in
petshas a foreign key to
people, and enforce the uniqueness by species using code external to the database.
Any alternate ideas?