I have Tags and Articles in a many-to-many relationship - Tags form a
taxonomy for Articles. This taxonomy is then used to build the
navigation of a site displaying the information in Articles. Thus Tags
need to be given relations - a Tag can have many child Tags, and a
child Tag can have many parent Tags. For example, the Tag 'USA' might
have the child 'News', but that Tag also has the parent 'Australia'.

I began with an Articles table, a Tags table, a Tags_Articles_Relations
table, and a Tags_Relations table. The first Relations table allows
for the many-to-many relationship between articles and tags. The
second Relations table had Tag_ID and Parent_ID as foreign keys to ID
in the Tag table. This table made a composite PK from Tag_ID,
Parent_ID, & Rank, allowing a Tag to have a different rank under
different parents.

However, this causes problems for querying - to know the page the user
is on, I would need to always know the complete path of Tag IDs to the
tree's root.

Can I, instead, give each relationship (Tags_Relations tuple) a PK
attribute 'ID'? Note that each tree branch (Relation) now has a unique
ID, and a one-many relationship with child branches, meaning that
instead of having to know the complete path to the tree's root, I just
need to know the ID of the relation between the current Tag and it's
parent, and the Relation ID of the parent Relation of that relation.

Thus, if I add a further table 'Relations_Relations', with FKs
Relation_ID and Parent_ID, I will be able to use this to traverse the
tree?

Is this a solid database design? Explanatory diagrams below:
http://www.intraspin.com/tree.gif
http://www.intraspin.com/DBdiagram.gif

Many thanks,
Iain

isporter (AT) gmail (DOT) com wrote:
Do you ever want the set of News articles regardless of parentage?
If you answer yes to the first and no to the second question, then you
have a real tree and you need to get and implement Joe Celko's "Trees
and Hierarchies in SQL for Smarties". Otherwise, what you have is a
general directed graph, which has (as far as I know) no good, general
representation in SQL.

If my understanding is corrent, the article sets are not disjoint
because articles may be tagged with both Australia and USA.

I don't believe I will ever want to display articles in a manner not
based on their tag metadata.

Say I have a root of 1, with children 2, 3, 4. Both children 2 and 3
can have the child 5.

You are suggesting that there is no good general representation in SQL
for this? What should I be considering to accomplish this data
structure?

Thanks,
Iain

Bob Stearns wrote:

isporter (AT) gmail (DOT) com wrote:
directed graph, or at least that is what I gathered from some items in
the newsgroups from him about two months ago. I certainly am not current
on research in this area, so maybe Google searching on "directed graphs"
and sql might be of some benefit.

BTW, if your taxonomy is not too deep, you might consider a table with
all the taxonomy levels and the article id (with the whole row as
primary key). This is not scalable wrt taxonomy or good sql practice (it
violates both the "array" proscription and the redundancy proscription),
but if your taxonomy is reasonably static and you have some type of
deadline, then it is a workable option.