Sql-server – How to model and enforce constraints on a categorical item contained in a collection

azure-sql-databaseconstraintdatabase-designsql server

Scenario:

There is a collection of items where each item belongs to 1 category.
There is a collection of buckets which may contain at most 1 item from each category. Oh, and an item may belong to at most 1 bucket.

I'm trying to model this with 3 sets at the moment:

  • categories (category_id),
  • items (item_id, category_id), and
  • buckets (bucket_id, category_id, item_id)

With

  1. a foreign key reference between items.category_id and category.category_id,
  2. a foreign key reference between buckets.category_id and category.category_id,
  3. a foreign key reference between buckets.item_id and items.item_id, and
  4. a unique index on (bucket_id, category_id)

But I'm stumped trying to prove that the item_id belongs to the category indicated by the category_id on the buckets set.

Am I going about this the wrong way? Is there a formal name for this problem? Thoughts?

EDIT: To make this more concrete, consider the following question which asks a similar question in terms of people and pet species (https://dba.stackexchange.com/a/16854/35832), but now lift the constraint to genus (genus is a grouping of species). That is, at most one of each genus, but the item in the bucket is a member of a species.

Best Answer

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

There is a collection of buckets which may contain at most 1 item from each category.

A unique constraint on bucket_id and category_id in items.

Related Question