Nrthugu

I'm storing a tree of small strings in Postgres. It looks like this:

"Languages"

    |

    |--- "French"

            |

            |--- "Verbs"

            |--- "Pronunciation"

    |--- "German"

            |

            |--- "Pronunciation"

            |--- "Cuisine"

"Music"

   |

   |--- "Guitar"

   |--- "Voice"

           |

           |--- "Breathing Exercises"

"Reminders"

"Vehicles"

   |

   |--- "Car"

          |

          |--- "Road Rules"

                    |

                    |--- "Fines"

                    |--- "Highways"

   |--- "Bike"

           |

           |--- "Repair Guide"

You get the idea. These are tag names; there will be another table full of flashcards/notes each of which is associated with one or more of these tags. One note might be tagged Languages.French.Verbs and Reminders for example, or a note about Queen Elizabeth might be tagged People.Historical and Countries.UK.History (where a . indicates a level in the hierarchy). In my application I want to browse the list of tags and their associated notes like a filesystem (tags as folders, notes as files within them) and see the same note appearing at multiple points because of these multiple tags.

I've been researching Postgres, which I'm not very familiar with (my classes used SQLite), and I can imagine two ways of accomplishing this -- but I'm not sure which is ideal/correct or what the tradeoffs are. Would love some advice about that.

When the user searches for some notes (either by tag name, note title, or text content) I want to show them a list of the results, and the full hierarchy of the tags associated with that note. As in, they search "Elizabeth", and note #1 is tagged People.Historical and Countries.UK.History. If I get the search results by searching in their notes table, and each note stores one parent ID, then how do I efficiently build up the full tag names?

Should I store a parent ID with each tag and do recursive queries? Would that necessarily be a separate recursive query for each leaf-node tag I'm looking up (as in one query for History and its parents and one for Historical)?

Would it be better if I stored two arrays with each tag, one with children and one with parents? That would avoid needing to do complex lookups each time, but adding and removing children would become more complex. If I expect to do 100 lookups for every add or remove, does that make sense, or is this a stupid idea?