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)
- a foreign key reference between items.category_id and category.category_id,
- a foreign key reference between buckets.category_id and category.category_id,
- a foreign key reference between buckets.item_id and items.item_id, and
- 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.