Re: File System Data Model

From: Baseman <>
Date: Wed, 19 Mar 2003 01:35:22 GMT
Message-ID: <uPPda.57805$>

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" <> wrote in message
> 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 Wed Mar 19 2003 - 02:35:22 CET

Original text of this message