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 2 3417
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: marv |
last post by:
If I have abstract base classes like these:
//---------
class IBase
{
public:
virtual void Action(void) = 0;
};
|
by: Steve Jorgensen |
last post by:
A while back, I started boning up on Software Engineering best practices and
learning about Agile programming. In the process, I've become much more
committed to removing duplication in code at a...
|
by: Steve Jorgensen |
last post by:
Recently, I tried and did a poor job explaining an idea I've had for handling
a particular case of implementation inheritance that would be easy and obvious
in a fully OOP language, but is not at...
|
by: Mark O'Flynn |
last post by:
I would like some advice regarding implementing inheritance using vb.net.
I have an application written in vb6 that I am rewritting from the ground up
in vb.net to take full advantage of .net,...
|
by: Jackson |
last post by:
I've got an inheritance question and was hoping brighter minds could
guide me. I am in the strange situation where some of the methods in a
subclass are actually more general than methods in a...
|
by: RSH |
last post by:
Hi,
I am working on some general OOP constructs and I was wondering if I could
get some guidance.
I have an instance where I have a Base Abstract Class, and 4 Derived
classes. I now need to...
|
by: Bart Simpson |
last post by:
I remember reading on parashift recently, that "Composition is for code
reuse, inheritance is for flexibility" see
(http://www.parashift.com/c++-faq-lite/smalltalk.html#faq-30.4)
This confused...
|
by: John |
last post by:
Hi All,
Although C# has Generics, it still does not support the generic
programming paradigm. Multiple inheritance is required to support real
generic programming. Here is a simple design pattern...
|
by: Leo Seccia |
last post by:
Hello everyone,
I have a c# project with a sql server database.
I have a number of lookup tables in my database which I successfully managed
to import into my LINQ dataclasses.
eg.
Table:...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: tracyyun |
last post by:
Hello everyone,
I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: nia12 |
last post by:
Hi there,
I am very new to Access so apologies if any of this is obvious/not clear.
I am creating a data collection tool for health care employees to complete. It consists of a number of...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
|
by: GKJR |
last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
| |