I'm developing a system that supports creating accounts and multiple users within every account that can each access a certain subset of data of their "parent" account.
The data is stored in a SQL Server database in the following tables:
Accounts: Id(guid, primary key), Title(nvarchar(200))
Users: Id(guid, primary key), Login(nvarchar), AccountId(guid, foreign key).
Now, I need to implement a concept of "Folders" and "Files" within accounts. Each account can have a number of files (an object with a name and a few other non significant properties) that can be arranged into folders (like a file system), and every user might have a permission to access a folder. "A user has permission to view a folder" means that this user can see all files within this folder and within all its subfolders. These folders/files also have to support a lot of moving around the hierarchy, renaming, editing, etc.
I have come up with the following table structure for this scenario:
Folders: Id(guid, primary key), Name(nvarchar(200)), AccountId(guid, foreign key)), ParentFolderId(guid, foreign key)
Files: Id(guid, primary key), Name(nvarchar(200)), FolderId(guid, foreign key),
UserFolderPermissions: Id(guid, primary key), FolderId(guid, foreign key), UserId(guid, foreign key)
This structure does the job of storing data correctly, but I also need to run some complex queries against it, like finding a complete path to a file or retrieving all files that current user has permissions to access. For that, I created a view that outputs all folders, files, and users who have permissions to access them:
WITH FolderWithPath AS ( SELECT folder.Id, folder.Name, folder.AccountId, folder.ParentFolderId, CAST('/' + folder.Name + '/' AS NVARCHAR(4000)) AS Path FROM dbo.Folders folder WHERE (ParentFolderId IS NULL) UNION ALL SELECT self.Id, self.Name, self.AccountId, self.ParentFolderId, CAST(parent.Path + self.Name + '/' AS NVARCHAR(4000)) AS Path FROM dbo.Folders AS self INNER JOIN FolderWithPath AS parent ON self.ParentFolderId = parent.Id ) SELECT file.Id AS FileId, folder.AccountId, folder.Id AS FolderId, folder.Path AS Path, userFolderPathPermissions.UserId AS AuthorizedUserId FROM FolderWithPath AS folder LEFT OUTER JOIN dbo.Files AS file ON file.FolderId = folder.Id LEFT OUTER JOIN ( SELECT UserFolderPermissions.UserId, folder1.Path, folder1.AccountId FROM FolderWithPath folder1 INNER JOIN UserFolderPermissions AS UserFolderPermissions ON folder1.Id = UserFolderPermissions.FolderId ) userFolderPathPermissions ON folder.AccountId = userFolderPathPermissions.AccountId AND folder.Path LIKE userFolderPathPermissions.Path +'%'
Now, this does seem to work, but I have certain performance concerns. Is it the best database structure to fit my needs? Is the query inside the view the optimal way to access the necessary data?
Due to different security issues, I am unable to test it in a real environment with millions of users performing hundreds of operations per second, so I would appreciate any advice regarding the code above.