Re: File System Data Model

Do a search for "Trees in SQL" on Google. You'll find a lot of references to Joe Celko's Nested sets model which is deemed to be optimal for finding paths thru parent-child related nodes... something an adjacency model doesn't accomodate too well. The drawback to the nested sets model are inserts and updates.

Good luck.

"Peter" <junk_at_wendler.org> wrote in message news:6a49813.0303180719.257b18d6_at_posting.google.com...
> I am attempting to create a db schema which would allow me to
> replicate a file system, i.e., locations of files within folders and
> folders within other folders. If anyone has an elegant solution, or
> can point me to a whitepaper on the subject, I would be very grateful.
>
> One of the areas with which I am having some difficulty is the
> creation of a complete path for a file which includes all of its
> parent folders back to the root share point or drive letter. Since
> there are n number of folders between the root and the file, it would
> seem that some kind of recursion would be needed to assemble the path.
> An alternative would be to de-normalize the data by maintaining a
> many-to-many relatoinship between each file or folder and all of its
> parents. This obviously creates some problems maintaining integrity,
> as after any folder is moved, all of its children and children's
> children must be updated.
>
> Thanks in advance for any help.
>
> Peter
Received on Tue Mar 18 2003 - 19:35:22 CST