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

Question

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?

Answer 1

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.

Answer 2

This is a junction table, and your requirements are handled by the natural key on the Pets table. In this case you have a many-to-many relationship between people and pet species.

Enforcing the rules become more complicated if you allow people to replace pets over time. In that case, you need to add a third column (I usually use a date type in this case) to the primary key and use a trigger to enforce having only one pet of a species at a time. A common real-life example would be employees in positions.

A related case is order details. In this case an order will have one or more detail lines for different products. (I usually use a line_id incrementing for each order.) The same uniqueness may apply to products, and can be handled by a unique key on the relationship.

Pseudo code for the simple case:

TABLE persons
    person_id int, auto increment, not null
    ...
    PRIMARY KEY (people_id)

TABLE species
    species_id int, auto increment, not null
    ...
    PRIMARY KEY (species_id)

TABLE pets (people_species)
    people_id FK(people), not null
    species_id FK(species), not null
    .... 
    PRIMARY KEY (person_id, species_id)