Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 (http://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.

share|improve this question
    
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 at 9:34

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
A filtered index with a unique constraint is exactly what I needed. Thank you. –  davidzarlengo Mar 23 at 2:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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