Ask a Question
Advertise on boostr.in
hierarchical tagging in sql using -'sql,mysql,database,tags,normalizing'
hierarchical tagging in sql ¬†using -'sql,mysql,database,tags,normalizing'
I have a PHP web application which uses a MySQL database for object tagging, in which I've used the tag structure accepted as the answer to this SO question.
I'd like to implement a tag hierarchy, where each tag can have a unique parent tag. Searches for a parent tag T would then match all descendants of T (i.e. T, tags whos parent is T (children of T), grandchildren of T, etc.).
The easiest way of doing this seems to be to add a ParentID field to the tag table, which contains the ID of a tag's parent tag, or some magic number if the tag has no parent. Searching for descendants, however, then requires repeated full searches of the database to find the tags in each 'generation', which I'd like to avoid.
A (presumably) faster, but less normalised way of doing this would be to have a table containing all the children of each tag, or even all the descendants of each tag. This however runs the risk of inconsistent data in the database (e.g. a tag being the child of more than one parent).
Is there a good way to make queries to find descendants fast, while keeping the data as normalised as possible?
Sep 7, 2015
to add a comment.
Your name to display (optional):
Email me at this address if my answer is selected or commented on:
Email me if my answer is selected or commented on
Privacy: Your email address will only be used for sending these notifications.
To avoid this verification in future, please