"Tim Mackey" <ti*@scootasp.net> wrote in message news:<bf**********@kermit.esat.net>...
hi folks,
i'm puzzled over this one, anyone with some solid db experience might be
able to enlighten me here.
i'm modelling a file system in a database as follows, and i can't figure out
to cleanly implement an inheritance mechanism.
i have a hierarchy of folders in an sql table. every folder has a
parentFolderID, if this value is 0 then it means it's a root folder.
then, in a 'files' table, every file has a parentFolderID to give it a
location in the structure. fairly basic.
the hard part is that each file record has an attribute 'STYLE' that can be
explicitly specified, or inherited from it's parent folder, or it's parents
parent folder, or.. all the way back to the root.
the 2 ways i've come up with representing it are:
1) if the style is being inherited, enter a null value in the STYLE field.
then to figure out what style applies to a file or folder, i trace back
through it's parentFolderID records until i find a style attribute that is
not null.
the good thing about this is that if i change the style that is applied to
the entire filesystem, it only takes one update.
the bad thing is when i want to figure out what style applies to a file, i
have to traverse back through possibly several records to locate the folder
that actually specifies the style being inherited by the file.
2) explicitly state all style values in each record.
this is good for accessing the style of a file or folder because you get it
straight out first time from the db.
the bad thing is if i update the entire file system, i might have several
hundred / thousand update sql statements to execute to update all the values
in every folder and every file. nasty!
thanks for any help, i'm really stumped with this and i'm thinking there
must be a more elegant way to implement inheritance.
thanks
tim
It would be good to see your table structure (DDL) and some sample
data - without that, this is essentially guesswork. However, based on
what you say, I'd go for option 2. First, from a design point of view,
if a file always has a style attribute in the filesystem, then it
should always have it in the database. Second, by always storing the
attribute, you give yourself much more flexibility for modifying the
style for a single file, for all files ending in ".txt", for all files
in 2 random subfolders etc. Third, walking the tree is probably a
relatively expensive operation, however you've chosen to model your
hierarchy. Fourth, you will have to 'cascade' your modifications
somehow anyway, to prevent the situation where a subfolder has a
different style from its parent, but you want the parent style to
apply to all children.
You seem to say that you would need a thousand UPDATE statements to
update the style attribute for a thousand files. Hopefully that's not
the case - you should be able to modify all the rows with a single,
set-based operation. Again, though, without knowing how you've
modelled the hierarchy, it's not possible to say for sure.
Hierarchies can be awkward in SQL, because they don't fit the
relational model particularly well. If you haven't already read the
chapters on hierarchies in "SQL for Smarties" (Celko) and "Advanced
Transact-SQL for SQL Server 2000" (Ben-Gan/Moreau), then I recommend
you do, if only to get some different perspectives on how to manage
them.
Simon
