Tag Magic

tag_magic.txt

tags
  id
  tag

On update of tag create synonym

synonyms
  tag_id
  synonym
  unique(tag_id, synonym)

definitions
  tag_id
  definition

entries
  id
  entry
  popularity

Popularity is some expression of number of views, downloads ...

entry_map
  tag_id
  entry_id

If on insert entry_id would be unique then slip the insert and do nothing
Otherwise, select all existing tag id's for that entry id and do
  foreach other_tag_ids
    insert tag_id, other_tag_id
    insert other_tag_id, tag_id
into tag_map.

On entry_map row deletion decrement appropriate rows in tag_map, as above

tag_map
  m_tag_id
  s_tag_id
  count (default 1)
  unique(m_tag_id, s_tag_id)

On unique constraint violation increment count
If on update count becomes 0, drop the row

----------

Starting with either an entry or a "category", it boils down to a master
or root tag_id.

Pull from tag map all s_tag_id's based on m_tag_id, which is our master.
Those with a count greater than the number of occurences of our master
tag_id in entry_map are our possible parents. Those with a lower count
are our possible children. (categories only)

Direct descendants of a category come directly from entries where tag_id
is our master tag_id, ranked possibly by a popularity metric.