I have a situation that after an initial thought ended in the following tables:
section: id, name section_tag: id, name section_tag_map: section_id, tag_id item: id, name item_tag: id, name item_tag_map: item_id, tag_id section_item_map: section_id, item_id
This describes well the data: items belong to sections. Sections and items have multiple tags for each of their "predicates", but they are different kinds of tags (section tags are specific to sections, item tags to items).
I will need to do this kind of query:
- Select sections with section_tag's (3, 4). Easy.
- Select items with item_tag's (1, 2) that belong to a section with section_tag's (3, 4). It didn't take long to realize what a mess of queries this requires.
I'm planning to simplify the initial idea, setting a single tag table and through app logic make items "inherit" tags that are meant to be set for sections. So it will be more like this:
section: id, name item: id, name tag: id, type, name section_item: section_id, item_id section_tag_map: item_id, tag_id item_tag_map: item_id, tag_id
The upside is that queries will be much simpler. The downside is that I'll duplicate data and have to take care of syncing item tags when the tags from a section change.
What I'm weighting is if I should worry about having a monster query like this (first design):
SELECT i.* FROM item AS i INNER JOIN section_item_map AS sim ON sim.item_id = i.id INNER JOIN (SELECT s.* FROM section AS s INNER JOIN section_tag_map AS stm ON stm.section_id = s.id WHERE stm.tag_id IN (3, 4) GROUP BY s.id HAVING COUNT(s.id) = 2 ) AS s ON sim.section_id = s.id INNER JOIN item_tag_map AS itm ON itm.item_id = i.id WHERE itm.tag_id IN (1, 2) GROUP BY i.id HAVING COUNT(i.id) = 2
...or a much simpler one like this (second design):
SELECT i.* FROM item AS i INNER JOIN item_tag_map AS itm ON itm.item_id = i.id WHERE itm.tag_id IN (1, 2, 3, 4) GROUP BY i.id HAVING COUNT(i.id) = 4
Am I on the right path or maybe I am missing something crucial here? The system is a single-user Sqlite app with about 20k sections and 100k items.