SQL Server 2008 - Document storage schema's

Question

Hey,

I'm looking to make a hopefully rather simple document storage system in sql 2008. We have a general idea of the elements we need, some meta data storage, filesteam, etc, but there are a few things we aren't quite sure of.

Specifically, we would like to implement a fake folder structure, as well as some (flexible) permissions. Permissions could be on a group level or by individual users, and should we should be able to specify no access, read, read/write, on either file level or folder level.

I'm not looking for someone to write this schema for me. But what I am hoping for is someone has resources that would cover these topics?

Thanks ~Prescott

Answer 1

Have you looked at FILESTREAM Storage in SQL Server 2008?

Answer 2

I think you should go with the classic route of having a documents table that would hold the docs (If using 2008 or above look at FILESTREAM). The meta tables would then link to that.

Your folder structure could be achieved by having a folder table, the material table could then have a field to show which folder the material is in.

To get the sub folder levels you would just have a parent folder field in your folders table self linking back to the same table. You can then render that up in a treeview control in what ever flavour language you wanted