Re: Adjacency List or Nested Sets to model file system hierarchy?

On Mon, Feb 12, 2007 at 10:53:53AM -0500, Merlin Moncure wrote:
> On 2/12/07, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> >> Can you describe in a little bit more detail about what you mean by
> >> 'Adjaceny LIst'?
> >
> >Adjaceny list is the term used in the celko book to refer to a table that
> >is recurively related to
> >itself.
> >
> >create table foo (
> >id integer primary key,
> >parentid integer references foo (id),
> >name varchar not null,
> >);
>
> Above approach is ok but I can think of at least two other methods
> that are probably better. First approach is to just store the whole
> path in every record for each file. Yes, this is a pain for updates
> but searching and children discovery is simple. in that case I would
> define pkey as (path, file).

Yes, that's what I meant by using a de-normalized table -- including
the full path in the row. That would provide fast access to each row
via a path name. And the parent id makes it easy to find all children
of a given node and, well, the parent too.

Separating the path and file as you suggest would make finding all
"files" at a given directory level simple, too.

But, I'm not thrilled about the possibility of the hard-coded path not
matching the path up the tree to the root node, though. Which, of
course, is why I posted. But, I'll give it a test.

Thanks,

--
Bill Moseley
moseley(at)hank(dot)org