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:
Plant
id | name | category |
---|---|---|
1 | ginger | medicine |
PlantTags
id | plant_id | tag_id |
---|---|---|
1 | 1 | 1 |
2 | 1 | 2 |
Tags
id | type | value |
---|---|---|
1 | body part | stomach |
2 | disease | IBS |
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?
EDIT:
Users should also be able to categorise a plant without tagging it. For example:
Plant
id | name | category |
---|---|---|
2 | cedar | timber |
EDIT 2:
Here is an example of a set of tags that should not be allowed:
id | type | value |
---|---|---|
1 | body part | stomach |
2 | disease | pneumonia |
pneumonia
is a disease of the lungs, not the stomach, so having the two tags stomach
and pneumonia
should not be allowed.
PlantTags
EAV table) - I doubt that additional tables/FKs may solve.