On 2/12/07, Bill Moseley <moseley@xxxxxxxx> wrote:
On Mon, Feb 12, 2007 at 10:53:53AM -0500, Merlin Moncure wrote:
> On 2/12/07, Richard Broersma Jr <rabroersma@xxxxxxxxx> 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.
The way I do it is to update the path to the parent's path, plus my id
on insert or update with a before trigger. I have an after trigger
that simply updates any child record's parent_id, which forces an
update of the path, which forces update of their children, and so on.
You can, of course, cause a recursion problem if you're not careful...
Best to have a check for that too.
- Ian
Thanks,
--
Bill Moseley
moseley@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
