Is there a name for this "restricted one to many" relationship? Can it be enforced in the DB?

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 people to 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 people for each possible pet's foreign key - a cat_id and 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 pets has a foreign key to people, and enforce the uniqueness by species using code external to the database.

Any alternate ideas?

I don't know that there's an official or technical name for the relationship you're describing (I also don't know if you'll find many pet lovers who would like to be restricted to one dog, one cat, etc.), but here is how I would probably approach this:

CREATE TABLE dbo.People
(
  PersonID INT PRIMARY KEY
  --, ... name, etc.
);

CREATE TABLE dbo.PetTypes
(
  PetTypeID INT PRIMARY KEY
  --, ... species name, etc.
);

CREATE TABLE dbo.PeoplePets
(
  PersonID INT, -- FK to dbo.People
  PetTypeID INT, -- FK to dbo.PetTypes
  --, ... pet name etc.
  PRIMARY KEY (PersonID, PetTypeID)
);

Now whether this is simple enough, I'm not sure. You might have different attributes for different types of pets, and so you may need to extend this, but I think in general the concept is sound.

StackExchange Database Administrators Q#16843, answer score: 11