Best way to store hierarchical tags

Question

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

For example, an entry in the list that says cars can have labels called cars, vehicles, ferrari.

I should be able to view the tag hierarchy as shown below. In addition, there should be no restrictions on the number of tags per entry, nor on how deep the tags can be.

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

Answer 1

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

Managing hierarchical data in MySQL is a pretty good article on hierarchical data structures. I believe that most of them can be applied to other database systems.

Answer 2

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

Answer 3

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

The only data is your entries in the list, which seem linear, not hierarchical.

For example, a list of films.

Another data source, its collection of hierarchical data ("tag 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 film can be associated with several styles of film:

• "StarWars: Phantom Threat": {"SciFi", "SomeDrama", "SoftHorror", "SomeAction"}
• "StarTrek: first contact": {"SciFi", "SomeDrama", "SomeComedy"}

From a database design perspective, you should have 3 tables or Entity Objects:

• List of entries = {ListEntryID, ListEntryTitle, ...}
• Genres for movies Tags / Styles = {TagID, TagTitle, ...}
• Styles for the movie = {TagForListEntryID, ListEntryID, TagID, ...}

Good luck.

Answer 4

Use the XML format, which will help you store nodes as parent and child nodes. It can have n number of nodes and is easily formed and processed. Note. The following is an example. This way you can process the data.

 <Menu> <Menuitem1> <submenu1> <submenu1> <submenu1.1/> </submenu1> </submenu1> </Menuitem1> <Menuitem1> <submenu1> </submenu1> </Menuitem1> </Menu> 

I think this may help you.

Answer 5

Here's how I would approach the problem: firstly, I will draw a domain model. In your case, it looks like this:

 List(1)----contains----(0..*)-->ListItem ListItem(0..1)----hasTags--(0..*)-->Tag Tag(0..1)-----hasSubTags---(0..*)-->Tag 

This makes the problem clearly beyond doubt.

Now translate this into a data model. This is pretty simple: for each relationship, enter the appropriate PrimaryKey-ForeignKey mappings. Many-to-many relationships should be split into two 1-M relationships using a new table between them.

The data model that you have at this stage should be functionally correct, but may have performance issues. It is time to focus on the queries you want and optimize the table structure accordingly.

(Another similar routing starting with a domain model will also give you design for the final class model)

Hope this approach helps.

Answer 6

See my answer here . I keep parents at all levels - creating a tree and querying all descendants is extremely simple.