Best way to store hierarchical tags

Question

I have a list, where each list entry is tagged with multiple tags. Each tag can also have child tags. Every entry in the list can have more than one tags.

For example, a list entry that talks about cars can have tags called "cars", "vehicles", "ferrari".

I should be able to view a hierarchy of tags, like shown below. Also, there should be no limit to number of tags per entry, and also how deep the tags can go.

How do I store this data? I am open to using any type of DBMS.

Answer 1

The naive approach would be a parent / child solution, but it's very difficult to write efficient queries with this data model.

Managing Hierarchical Data in MySQL is a pretty good article about hierarchical data structures. I suppose most of it can be applied to other database systems, too.

Answer 2

I think this is the simplest way for any database: tag (id, name, parent_id), where parent_id refers to id of the parent tag.

Answer 3

You are using 2 sources of data, but, seems you are mixing both.

One data is your list entries, that seems to be lineal, non hierarchical.

For example, a list of movies.

The other source of data, its a collection of hierarchical data ("tags catalog").

For example a list of movie styles.

+---Styles
  +---Comedy
    +---KidsComedy
    +---SomeComedy
    +---LOLComedy
  +---Action
    +---SomeAction
    +---GrabYourCouchSofaAction
  +---Drama
    +---SomeDrama
    +---LotsOfTearsDrama
    +---EvenToughGuysWillCryDrama
  +---Horror
    +---SoftHorror
    +---HardHorror
    +---Gore
  +---SciFi

Each movie can be associated with several movie styles:

• "StarWars:The Phantom Menace": {"SciFi,"SomeDrama","SoftHorror","SomeAction"}
• "StarTrek:First Contact": {"SciFi,"SomeDrama","SomeComedy"}

In terms of Database design, you should have unleast 3 tables or Entity Objects:

• List Entries = {ListEntryID, ListEntryTitle, ...}
• Movie Genres Tags / Styles = {TagID, TagTitle, ...}
• Styles For Movie = {TagForListEntryID, ListEntryID, TagID, ...}

Good Luck.