inheritance advice in a database...

[Tim Mackey]

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

[Simon Hayes]

"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

[Tim Mackey]

hi simon,
thank you very much for the reply. it's very helpful.
you're right about the updates, i confused the words... i meant to say that
i'd need to do one update command that would update 1000 records, which
probably isn't so bad, considering the rarity with which this operation will
be done.
i haven't read the sql chapters you mentioned. i rarely come up with db
design difficulty like this but it sounds like a useful book on the shelf.

your advice is very helpful and i think i'll go with what you suggest.
just for information, my table structures are as follows:

CREATE TABLE `Folders`
(
`ID` INTEGER (11) NOT NULL AUTO_INCREMENT ,
`ParentFolderID` INTEGER (11) NOT NULL DEFAULT 0,
`Name` varchar (50),
`Style` INTEGER (11),
PRIMARY KEY (ID)
)

CREATE TABLE `Files`
(
`ID` INTEGER (11) NOT NULL AUTO_INCREMENT ,
`ParentFolderID` INTEGER (11) NOT NULL DEFAULT 0,
`Name` varchar (50),
`Style` INTEGER (11),
PRIMARY KEY (ID)
)

for sample data, imagine 15 folder records with auto IDs, the first 10
records have the same parentFolderID of 0, meaning they are at the root
level, and they have names for the folders. the next 5 records have
parentFolderID=1 meaning that these folders are directly under the first
folder under the root.

cheers
tim
--
-------------------------------
Tim Mackey
www.ScootASP.net // .NET web hosting
ti*@scootasp.net
-------------------------------

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:60**************************@posting.google.c om...

"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