3

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.

5
  • Please select only one DBMS. If you need for both then create 2 separate questions. These DBMSs instruments are too different.
    – Akina
    Feb 8, 2021 at 11:02
  • Sorry, I've put it as MySQL. But a high-level conceptual answer would be fine Feb 8, 2021 at 11:14
  • Conceptual answer seems to be in disabling direct access to the data. All data changes will be performed via stored procedures which realizes and checks any logiс which you need. Fast implementation may be in trigger logic usage, but it is difficult-to-expand/check/control.
    – Akina
    Feb 8, 2021 at 11:21
  • I think the problem could also be solved with changes in design only (adding new tables and foreign keys). Feb 8, 2021 at 11:26
  • @ypercubeᵀᴹ OP needs in group check (check values combination in a group of PlantTags EAV table) - I doubt that additional tables/FKs may solve.
    – Akina
    Feb 8, 2021 at 12:13

2 Answers 2

4

There are a couple of ways you can go about this, but the most simplest way to enforce this would probably be to create a dimension combination table. Such a table is pre-filled with all valid possible combinations (by id) and then that is the source of your application's controls (such as dropdowns, etc) because as you filter on one dimension in that table, only valid entries for the remaining dimensions will be available.

For example, your dimension combinations table would have a plant_category_id column and a tag_type_id and maybe a second tag_type_id2 column (or if you normalized your Tags table, the column naming would be a little better in the dimensions combinations table).


The other thing you can do is implement a complex check constraint (possibly with a function) but this is probably more work than it's worth, and will depend on which implementation of MySQL and version you're using.


The third way is handling the logic in stored procedures that manage data access via the logic you want to implement, but probably would also be a lot of work, especially to maintain over time. So the first suggestion that makes it table driven would be my first pick.

5
  • I like that suggestion! Could you explain a little more about normalising the Tags table? I'm not sure I understand why tag_type_id2 is necessary. Feb 8, 2021 at 12:54
  • @Joaquimd'Souza I suggested normalizing because you said one of the requirements is that multiple Tags need constraints on each other too, e.g. IBS only goes with stomach. So in your dimensions combinations table you'd need a way to repsent them both in the same row, which can be done via tag_type_id and tag_type_id2 or if you normalize the Tags table since they are different types of tags technically, then you could have a BodyTags and a DiseaseTags table, and then your columns in your dimensions combinations table now become body_tag_id and disease_tag_id...
    – J.D.
    Feb 8, 2021 at 13:16
  • ...instead which improves readability. Also normalizing your Tags table improves query performance as well, generally. But you don't have to normalize if you don't want and still go with my first naming convention instead.
    – J.D.
    Feb 8, 2021 at 13:17
  • Ah I see - the issue there is there are 20+ types of tag, so then the combinations table would have 20+ columns... unless there were a different combinations table for every pair of types. For me I think that is too much normalisation...! But in any case I think your solution works. Feb 8, 2021 at 13:42
  • @Joaquimd'Souza Yea it just depends on your preference. It's not unusual for a dimensions combination table to be wide and denormalized in a sense. This is actually a pretty common design pattern in the financial world. But using the generic version with just tag_type_id and tag_type_id2 (nullable) works just as well.
    – J.D.
    Feb 8, 2021 at 13:47
2

You can model a hierarchy by using a foreign key that references the same table.

CREATE TABLE tag
             (id integer,
              parent integer,
              key varchar(64),
              value varchar(64),
              PRIMARY KEY (id),
              FOREIGN KEY (parent)
                          REFERENCES tag
                                     (id));

If a plant is then associate with one of the tags, all the tags higher in the hierarchy will be implicitly associated.

9
  • Thanks for the answer, but I don't think this will solve my question. Firstly, users should be able to select a category without choosing any tags. Secondly, this doesn't allow me to specify which combinations of tags are valid. Feb 8, 2021 at 12:07
  • @Joaquimd'Souza: Well, it perfectly solves your example. Maybe you can edit the question and come up with another example that cannot be solved by that approach.
    – sticky bit
    Feb 8, 2021 at 12:12
  • done, hope that is clear Feb 8, 2021 at 12:24
  • @Joaquimd'Souza: Edit my answer. Of course instead of just associating the leaves of the tag forest, users can also use any node, also a root node. Still solves your problem.
    – sticky bit
    Feb 8, 2021 at 12:35
  • I think there is still something missing - your answer doesn't provide a way to specify which combinations of tags are valid. I have updated my question to give an example of two tags that shouldn't be used together. They are both children of the medicine category. Feb 8, 2021 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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