Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you. I'm pondering what to do. Tag changes should be rare after a section is set. That may be a good reason to avoid the monster query, but in any case I'm testing both. I posted an untested query above, will check if it works now. –  moraes Apr 21 '13 at 1:06
    
I tested both solutions and decided for a mix: I will have a single tag table but will keep section_tag_map and item_tag_map separated. A third map table will mix item tags and "inherited" item tags, just for the purpose of queries. It will be sync'ed periodically, as needed. –  moraes Apr 21 '13 at 14:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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