Creating hierarchical information in a relational database

How do you set up tables to work?

[korkus2000]

I want to create a directory type script. I want to pull the information from a database. So I would have basically a couple of tables for catagory and listings. I have currently had a DB field that has catagory above and a field catagory below to show the hierarchy. This just seems counter intuitive though. How can I make these rows show hierarchy? These cats could also change their nesting place. Any suggestions?

[jpjones]

Not sure if I'm understandig your right (recovering from migraine - ouch), but if I am, you are after a table to display categories such as:
Top Level -> Sub cat -> Sub cat

Could you not set up your table like thus:

CatID ¦ Category ¦ ParentCatID

This way you have:

1 ¦ Computers   ¦ 0
2 ¦ Monitor    ¦ 1
3 ¦ Input Devices ¦ 1
4 ¦ Mouse     ¦ 3
5 ¦ Keyboard   ¦ 3
6 ¦ CPU      ¦ 1

Therefore Keyboard would be listed as:
Computers -> Input Devices -> Keyboard

JP

[Woz]

If you are after a pure Parent/Child Taxonomy then JP's example is correct. You then link listings to the required category.

If you are after more of a relational Ontology where a parent can have many shildren, and a child can have many parents (?), then you would need a third linking table to handle all the relationships.

I tend to use a limited Ontology by limiting myself to a 2 to many relationship, ie, a child can only have two parents, which seems to cover most situations, by adding a fourth field thus:-

CatID ¦ Category ¦ ParentCatID ¦ RelatedParentCatID

A little limiting but as I say it seems to cover most situations and would also be a little faster that a pure Ontological structure.

Onya
Woz

[webdevsf]

If you are using sql server, look in the Books online index for "Expanding Hierarchies". Has the code for materializing the hierarchy...

[ggrot]

Woz's example for the 2xMany isn't the best way to go. It violates what's known as normalization. The problem stems from the that there is no distinction between the two parents in his schema. They could both be swapped (Im assumming) and the same information would be there. So, if you were looking for a list of all nodes who's parent was '5', then you couldn't query that on one key. Its better to add another table that has a 1x1 ParentxChild relation in this case.
ie:

NODES TABLE
NODE ID ¦ NODE NAME ¦ NODE DATA

RELATION TABLE
PARENT ID ¦ CHILD ID

This also gives you the later flexibility of switching to 3 or 52 parents, or varying it per node - some nodes have 2, some have 6.

[victor]

I learnt all I needed to know about building flexible tree structures from this article:

[intelligententerprise.com...]