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.