Re: How to represent a tree-structure in a relational database

On Wed, Dec 13, 2000 at 04:48:47PM +0100, Frank Joerdens allegedly wrote:
> I am just thinking about the data model for a little content management system that I am
> currently planning. Individual articles are sorted under different categories which branch
> into subcategories, sub-subcategories etc. up to a depth of about 6 or 7 levels. The
> structure should be extensible, i.e. it must be possible to add levels. What I am thinking
> now is that you would keep the index in a separate index table (linked with the primary
> key in the articles table), which would have 6 or 7 fields initially, and that you'd add
> columns with the alter table command, if need be, to make the structure deeper. Is this
> the recommended way to go about it? It feels pretty 'right' to me now but since the
> problem should be fairly common, there must be other people who have thought and written
> about it and there might even be a recognized 'optimal' solution to the problem.
> 
> Comments?

Yeah. I've built something similar.

The way I've done it: Give each record a unique ID (generated with a sequence) and store the records in a table. Create
asecond table in which you store parent id-child id combinations.
 
 So:
   1 - Automotive transport   2 - Cars   3 - Motorcycles
   Store in the table:     1-2     1-3
 There's one main category (Automotive transport) which has two sub-categories:   Cars & Motorcyles

The way I'd do it if I had to do it again: Give each record a unique id, generated by the application. Denote levels
withextra letters.
 
 So:
  AA   - Automotive transport  AAAA - Cars  AAAB - Motorcycles
 The structures has the added bonus of making it very easy to determine all the sub-categories of a category, no matter
howdeep the tree is below the category you're looking at. With the first approach it is not possible to do this in a
singleSQL query. You could do this with a function, I guess.
 

I hope this is of some use to you.

Cheers,

Mathijs
-- 
"Borrowers of books -- those mutilators of collections, spoilers of thesymmetry of shelves, and creators of odd
volumes."       Charles Lamb (1775-1834)