Circular dependencies from constraints

Hey!

I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.

To achieve this ive created te following tables (truncated for clearity):

###################
# Sites #
###################
# ID [Int] #
# ... #
###################

###################
# Folders #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

###################
# Files #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################

Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!

Then i set up the foreign constraints as follows:
Folders.FolderD -> Folders.ID
Folders.SiteID -> Sites.ID
Files.FolderID -> Folders.ID
Files.SiteID -> Sites.ID

All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)

Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?

Hi Supermajs,

Firslty, I would remove the constraints.

Secondly, remove the SiteID and FolderID fields from the Folders and Files tables.

Thirdly, add the fields ParentID and ParentTypeID to the Folders and Files tables.

Then add a new table called tblParentType with the following records: -

ParentTypeID ParentType

1 Site

2 Folder

Then add a constraint between ParentType.ParentTypeID and Folders.ParentTypeID and between ParentTypeID.ParentTypeID and Files.ParentTypeID.

Now a record within Folders will look like this: -

ID ParentID ParentTypeID

1 3 1 this folder is a folder under site id 3

2 14 2 this folder is a folder under folder id 14

The same applies for the Files table.

By doing it this way, you can easily add new levels without needing extra fields that always contain NULL.

For example, you could add a new layer above site called server. The ParentType table would be as follows: -

ParentTypeID ParentType

1 Server

2 Site

3 Folder

|||

.In relational modeling it is files and association, I think you have designed flat files that could be a problem because DRI(declarative referential integrity) comes with restrictions. If you have 50 files that are associated they belong together that is how you can start with 100 files and end up with 5 tables. Try the Normalization tutorial and free data models to clean up your design and post again so I can help you with the DRI Cascade operation because it comes with fixed requirement of if A references B then B must exist. Hope this helps

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

http://www.databaseanswers.org/data_models/