Re: File System Data Model

From: Mikito Harakiri <>
Date: Tue, 18 Mar 2003 18:15:33 -0800
Message-ID: <WuQda.27$>

And if you want to handle inserts and updates, you can explore persistent labeling schemas such as materialized path and/or nested intervals (I'm not sure what name the author is using for his labeling schema in Also, be sure that you have your requirements right: if your filesystem has links (aka Windows shortcuts), you might want to model it as a network instead of tree.

"Baseman" <> wrote in message news:uPPda.57805$
> Do a search for "Trees in SQL" on Google. You'll find a lot of references
> 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 - 03:15:33 CET

Original text of this message