Database Design for Document Library

Question

We are attempting to built the Document Library (File Management) as apart of our core apps. It starts with simple one obviously.

I need a feed back regarding the database design for this particular module. Initial Design:

File table:

• FileID
• FileGUID
• TenantID
• FileName
• FileDescription
• FileImage
• FileSize
• FileExtension
• ContentType
• CreatedUTCTimeStamp
• CreatedIP
• CreatedBy

Folder table:

• FolderID
• FolderGUID
• FolderName
• FolderDescrption
• CreatedUTCTimeStamp
• CreatedIP
• CreatedBy

FolderFiles table:

• FolderID
• FileID

Any input that will be great. Possibly in the future to have workflow, permission(s), etc etc

Answer 1

Be sure to check out commercial solutions (eg, Oracle Content Management) and open source solutions (eg, Drupal). You really don't want to start from scratch on this one if you can avoid it.

A couple of points on your schema though ...

• You probably want to treat Folders as just a kind of File that can contain other Files. This allows you to have Folders in Folders, which is quite beneficial. For this you could omit the Folder table and just have a boolean field (Y/N) in File that says if this File is a Folder. There would be another File field that has the FileID of its containing Folder File. Your schema already points out the strong similariry of File and Folder. (But hierarchies like this are hard to model efficiently in RDBMSs.)

• There could be a default FileImage for each FileExtension, if FileImage is null. This would require another table keyed on FileExtension and also containing the FileImage.

Answer 2

Didn't someone design this database before?. WinFX... no... WinZS... no... Got it. WinFS.

Answer 3

Take a look at how UNIX file systems are designed. They have a number of inodes, which have no name on their own. All files, directories, symbolic links are a sub-type of inode. They all get their names only from the directory listing. This allows you do use the same file in multiple directories/using different names (this is called hard link in UNIX terminology).

Answer 4

Some suggestions:

• Add LastUpdatedTime/LastUpdatedIP/LastUpdatedName to all tables
• Consider a FolderFolders table
• Break the Files table into a Documents table and a Files table. As the system evolves there is a good change that you may add the ability to store content that are not files.
• Do not treat folders as a sub-class of file. These are two separate concepts and combining them makes evolving the system difficult.
• Be careful if you implement the Unix link concept. Most Windows users are used to folder security securing files as well. In your system, if a file can be stored in multiple folders, the file could be secured in one folder, but unsecured in another.