/* Secure File Storage */

/***********************/

ALTER TABLE {databaseOwner}{objectQualifier}Files DD

FolderID int NOT NULL CONSTRAINT DF_{objectQualifier}Files_FolderID DEFAULT 0,

Content image NULL

GO

/* make FolderPath values consistent with Files table Folder column */

UPDATE {objectQualifier}Folders

SET FolderPath = FolderPath + '/'

WHERE FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'

GO

/* Handle Updating Files table with new FolderID column based on Folders table */

UPDATE {objectQualifier}Files

SET FolderID = FO.FolderID

FROM {objectQualifier}Files F, {objectQualifier}Folders FO

WHERE F.Folder = FO.FolderPath

AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND FO.PortalID is NULL ) )

GO

/* This deletes the files with no valid folder Ids */

/***************************************************/

DELETE {objectQualifier}Files

WHERE FolderID Is NULL

GO

ALTER TABLE {databaseOwner}{objectQualifier}Files

DROP CONSTRAINT DF_{objectQualifier}Files_FolderID

GO

ALTER TABLE {databaseOwner}{objectQualifier}Files DD CONSTRAINT

FK_{objectQualifier}Files_{objectQualifier}Folders FOREIGN KEY

(

FolderID

) REFERENCES {databaseOwner}{objectQualifier}Folders

(

FolderID

)

GO

----------------------------------------------------------------

This procedure add new filed (FolderID) to Files table and create a reference beetwen Files and Folders tables.
Now, i'm a bit confused about this implementation:
both Folder and Files tables have a reference to Portals table by PortalID index,(this generate a confilict on creating a FK_Folders_Files reference).

Why don't we use this simple structure: [Portals] --> [Folders] (PortalID=PortalID)- [Folders]-->[Files] (FolderID=FolderID) ?
By using this structure, when user delete a Portal, all related folders and files will be deleted; same thing for delete Folder.

I wish someone can help me to understand.

Thanks in advance and sorry for my bad english.