Database design problem

I have a problem when the foreign key in a table points to the primary
key in the same table. Here is the script to create the tables:
CREATE TABLE [Folder] (
[FolderID] [int] NOT NULL ,
[MasterFolderID] [int] NULL ,
CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
(
[FolderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
(
[MasterFolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
CREATE TABLE [File] (
[FileID] [int] NOT NULL ,
[FolderID] [int] NOT NULL ,
[FileSize] [int] NOT NULL ,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
(
[FileID]
) ON [PRIMARY] ,
CONSTRAINT [FK_File_Folder] FOREIGN KEY
(
[FolderID]
) REFERENCES [Folder] (
[FolderID]
)
) ON [PRIMARY]
GO
So I have a "Folder" table as the master and a "File" table as the
detail, but the Folder table also has a one to many relationship to
itself.
Then I have the following data in these two tables:
INSERT INTO [Folder]([FolderID])
VALUES(1)
INSERT INTO [Folder]([FolderID], [MasterFolderID])
VALUES(2,1)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(1, 1, 10)
INSERT INTO [File]([FileID], [FolderID], [FileSize])
VALUES(2, 2, 10)
The "File" table has a field called FileSize that needs to be added to
give you the size of each folder. Here is the sql statement I use for
that:
Select FolderID,
(Select Sum(FileSize) From [File] Where [File].FolderID =
Folder.FolderID) As Size
From Folder
The problem with the above statement is that it only adds sizes of the
files in the folder and not the sizes of the folders in a folder.
I hope someone understands my problem and would realy appreciate some
help.To make sure:
Folder 1
File 1a 10 kb
File 1b 10 kb
Folder 1.1
File 1.1a 10 kb
File 1.1b 10 kb
You want the following:
Folder 1 40kb
Folder 1.1 20kb
Right?
That is done via recursion. Unfortunately, there is no simple way to
accomplish this in T-SQL (at least not until SQL Server 2005). To accomplish
this, you will have to curse through the hierarchy and create the aggregates
.
My advice, esp. if this is a large app: Denormalize slightly to add the size
to the folder. Then create a routine that curses through and gets the total.
Finally, create a trigger that updates totals when a new record is added.
NOTE: This assumes that this is not an oft updated/inserted table. If it is,
you can still get totals for the files and recude the amount of recursion
work necessary to get the final tally for each directory (reduce by one "sum
"
aggregate).
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Pierre" wrote:

> I have a problem when the foreign key in a table points to the primary
> key in the same table. Here is the script to create the tables:
> CREATE TABLE [Folder] (
> [FolderID] [int] NOT NULL ,
> [MasterFolderID] [int] NULL ,
> CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
> (
> [FolderID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_Folder_Folder] FOREIGN KEY
> (
> [MasterFolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [File] (
> [FileID] [int] NOT NULL ,
> [FolderID] [int] NOT NULL ,
> [FileSize] [int] NOT NULL ,
> CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED
> (
> [FileID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_File_Folder] FOREIGN KEY
> (
> [FolderID]
> ) REFERENCES [Folder] (
> [FolderID]
> )
> ) ON [PRIMARY]
> GO
> So I have a "Folder" table as the master and a "File" table as the
> detail, but the Folder table also has a one to many relationship to
> itself.
> Then I have the following data in these two tables:
>
> INSERT INTO [Folder]([FolderID])
> VALUES(1)
> INSERT INTO [Folder]([FolderID], [MasterFolderID])
> VALUES(2,1)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(1, 1, 10)
> INSERT INTO [File]([FileID], [FolderID], [FileSize])
> VALUES(2, 2, 10)
>
> The "File" table has a field called FileSize that needs to be added to
> give you the size of each folder. Here is the sql statement I use for
> that:
> Select FolderID,
> (Select Sum(FileSize) From [File] Where [File].FolderID =
> Folder.FolderID) As Size
> From Folder
> The problem with the above statement is that it only adds sizes of the
> files in the folder and not the sizes of the folders in a folder.
> I hope someone understands my problem and would realy appreciate some
> help.
>