Designing Database for File Structure

Question

We are using file system to store files within the application. Now we change this to use SQL2K5 for storing as BLOB instead as per requirement.

Now, we need advice regarding the design for table. Obviously, it must have a folder, files within files, size, last date modified, etc., similar to file system.

I start with:

FileID, ParentFileID, FileName, Size, LastDateModified, DateCreated, LastModifiedBy, ModifiedBy

How can this be modified to handle folders as well?

Answer 1

As Mitch Wheat said, there's a really good system for this already, and it's called the File System - my first recommendation would be to look at your requirements again to see if it is actually required.

However, you may have your reasons, so here's how i'd structure the table:

filesystem (
    id,        // auto increment
    type,      // flag field: 1 = file, 2 = folder, 3 = symlink, if needed (?)
    parent_id, // id of a folder
    filename,
    modified,
    created,
    modified_by,
    created_by,
    file_data    // blob
)

You'd need a unique index on (parent_id, filename) if you wanted to emulate a real system.

If you needed per-file permissions, I'd just duplicate the Unix approach with owner/group/everyone permissions - you'd need to track owner and group_id in that table too. Perhaps you could simplify it to owner/everyone, and you probably could just use read/write (forgoing "execute").

Answer 2

Please find the modified one:

FileSytemObjects(
FileSystemObjectID,
ParentFileSystemObjectID,
FileSystemTypeID, --File, Folder, Shortcut
Data,
DateCreated,
LastModified,
CreatedBy,
LastModifiedBy,
IsActive
)

FileSystemSecurity(
FileSystemObjectID,
GroupOrUserID,
IsAllowFullControl,
IsDenyFullControl,
IsAllowExecute,
IsDenyExecute,
IsAllowListFolder,
IsDenyListFolder,
...
...
)

With IsAllowFullControl, IsDenyFullControl, IsAllowExecute, IsDenyExecute, IsAllowListFolder, IsDenyListFolder, I know it's not an ideal of DB design BUT it's much quicker to get permission in one hit.

What do you think?