Database design: nested tagging

Question

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.

Answer 1

I don't see much difference in the two designs. Whether you have 2 tables for the tags or 1 table with a type attribute (that can take 2 values), you basically going to need the same (in matters or complexity and structure) queries.

What is different is your decision to materialize the relationship between items and the section_tags, the tags that are "inherited" to them through the relationship with the sections. But that can be done with either the first or the second design. And yes, it would simplify querying (on the expense of making harder to have the materialized tables/views in sync.)

So, about the choice of design, if as you say the item and the section tags are two different types of tags, either design would be ok and I don't think it would make much difference, except on the details in the interaction between applications and the database.

For the materialization part of question, I think the best would be to test both options. If you are to make a decision fast, you could take the first path. Keep the design simple and only if you have efficiency issues that can't be overcome, examine the option of materialization as it would be quite complicated (as you have already thought) to keep it synced.

Answer 2

In your scenario - is a tag literally just a tag with no other associated attributes specific to each type?

Unless there is something fundamentally different about a "section tag" and an "item tag" (e.g. if, as an object, a section tag has different characteristics than an item tag), then they shouldn't be split out (neither in separate tag tables, nor in separate materialized views).

If they are essentially the same e.g. a tag is a tag is a tag, then it makes more sense to unify - much like your 2nd option, but don't split out the tag maps into section_tag_map and item_tag_map - after all, these are just "[object]-tag maps"

You can just do:

tag_map (tag_id, obj_type, obj_id)

Where obj_type would be either 'section' or 'item' (or a representative id), and obj_id is the id from the appropriate entity.

Typically/generally I would then partition/cluster the index on obj_type to get performance.