Table and query opinons

[mbowles]

I have written my own docuemtn management system with c# and sql. I have a folder table (folderid, parentid, name, etc..) and a file table (file id, folderid, name, etc...). I then have foldersecurity table (folderid, userid, accesslevel) and well as one for filesecurity(similar to foldersecurity).
Obviously folders can contain folders and on downward as well as files at each level. Runs pretty well. Where I need opinions is on the security model. My users exist as indivudial users and groups of users from another set of tables. When an user set permissions on a folder and chooses the option to filter permsissions down through the folders contents (folders and files), my recursive stored proc takes 3-5 minutes to run. Any other ideas on how to handle file and folder permissions(read, write, set permissions are the three levels.)

 

[mrdenny]

It sounds like your permissions model is good.

Something has got to be wonky with your recursive stored proc. It shouldn't take 3-5 minutes to run. It shouldn't take 3-5 seconds to run. You should only be handeling a few dozzen records at most. Check your tables and your code, there is probably a better way to handle things.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mbowles]

Thanks. I just finished reviewing some open source versions and do things very similarly.

There are times when the sp can recurese through one parent, with 10-15 subs, and each sub have 10-15 for about 4-5 levels deep. Each with a handful of files. I will review it some more.

 

[SQLSister]

How do I do ___________ with a Hierarchical structure?
faq183-5322

This FAQ has some good links ofn recusion. They might help you find ways to improve your model.

Questions about posting. See faq183-874