I'm working on an SQL database of useful plants. I'd like to be able to allow users to annotate the plants with a set of hierarchical categories and tags. For example:
- category: medicine
- body part: stomach
- disease: IBS
An initial database structure could be:
I would like to restrict the categories and tags that can be used together. For example, one should only be able to apply the
body part and
disease tags if the
medicine category is selected. Furthermore, one should only be able to select the
disease: IBS tag if the
body part: stomach tag is selected.
Put another way, I want a way to specify which combinations of tags are valid.
Is this possible at the database level?
Users should also be able to categorise a plant without tagging it. For example:
Here is an example of a set of tags that should not be allowed:
pneumonia is a disease of the lungs, not the stomach, so having the two tags
pneumonia should not be allowed.