2

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.

  • Can an item have many buckets and a bucket belong to only one item (as your design suggests)? Or an item can belong to only one bucket and a bucket can have many items? – ypercubeᵀᴹ Mar 19 '14 at 9:34
2

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.

  • 1
    A filtered index with a unique constraint is exactly what I needed. Thank you. – davidzarlengo Mar 23 '14 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.