CODEWITHSUNDEEP
sql-serverdatabase-designdata-modeling
dcpartners
dcpartners

Reputation: 5446

Database Design for Document Library

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:

Folder table:

FolderFiles table:

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

Upvotes: 0

Views: 2147

Answers (4)

Darryl Peterson
Darryl Peterson

Reputation: 2270

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.

Upvotes: 1

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

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).

Upvotes: 1

DVK
DVK

Reputation: 129403

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

Upvotes: -1

Jim Ferrans
Jim Ferrans

Reputation: 31012

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.

Upvotes: 2

Related Questions