xyph Posted August 22, 2012 Share Posted August 22, 2012 I'm wondering what you guys think would be the best way to accomplish this. It's more of a design question than any specific query. I have a client that I'm building a file-management system for. They require a user/group permission system, and it's a little archaic. I'll try to explain Files are 'placed' in a folder using an alias to that file. Any file can have an alias in any number of folders. Folders/subfolders are placed in 'cabinets' using an alias to that folder/subfolder. Any folder can have an alias in any number of cabinets or folders. Permissions are applied to aliases, not the files/folders themselves. Different aliases of the same file could have different permissions. Permissions can target either a user, or a usergroup. As of now, I have 4 tables to link user/group permissions to file/folders. user_to_file_permissions user_to_folder_permissions group_to_file_permissions group_to_folder_permissions I like this method, because I can easily keep foreign key constraints. I don't like it though because getting them all into a single query could be quite ugly (if possible). I'm guessing I'd use UNIONs and possibly some if cases to help determine which values came from which tables. In the case of conflicting permissions, the most permissive will be used. Is there a better way to do this? Should I provide more details? Ideally, I'd like to keep foreign key constraints available, but if it's not possible in a more robust or efficient system, I can program the clean-up manually Quote Link to comment Share on other sites More sharing options...
fenway Posted August 23, 2012 Share Posted August 23, 2012 Well, you can add 'fake' columns with UNION. Quote Link to comment Share on other sites More sharing options...
xyph Posted August 23, 2012 Author Share Posted August 23, 2012 So the design isn't completely daffy? Or am I not giving enough information to make it clear? Yeah, I guess it's easy enough to specify extra constant columns in the UNION. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 26, 2012 Share Posted August 26, 2012 Besides, when are you forced to use a 'single query'? Quote Link to comment Share on other sites More sharing options...
xyph Posted August 26, 2012 Author Share Posted August 26, 2012 Agreed, just nice to get it all over with in the SQL so I don't have to bounce data back and fourth between PHP/SQL. After doing a bunch of testing and research, I ended up going with a node-style relationship anyways, using the calculated path to determine final permissions. OQGRAPH is quite the amazing tool. Thanks for the input, though. Quote Link to comment Share on other sites More sharing options...
fenway Posted August 26, 2012 Share Posted August 26, 2012 Care to elaborate on that node-style solution? Quote Link to comment Share on other sites More sharing options...
xyph Posted August 27, 2012 Author Share Posted August 27, 2012 Well, it was a very odd problem. The client wanted a per-alias based permission system, with a combined 'base security level' requirement, and possible user/group-specific permissions. They also wanted the folder structure (subfolders,etc) to go along with the alias - changes in one tree will affect all. I decided to go with a node-based system. Each source->destination (parent->child) is given it's own relationship. Any given child can have multiple parents. The engine makes it extremely easy to find any path, in any direction. On my development machine and dummy data, it can calculate over 24k distinct paths (20 roots, 5 levels deep, large amount of children with multiple parents) in less than 100ms. This should be way beyond any amount of data I'll ever need to get in a single query. I probably went overkill, but I was free to choose my own database engine, and MariaDB comes with OQGRAPH bundled. I probably could have done this 'fast enough' in Postgre/etc with recursion. For permissions, I simply reference the path from the root to the node, and restrict the results from the graph engine to those with a matching path. The way I've done it is not completely normalized, but since I can calculate any path from node-to-node so damn quickly, it's a non-issue to me. I could see a potential problem showing up if I ever needed to modify indexes, but that should be pretty straightforward to fix anyways. I don't like the fact that I can't use a foreign key constraint, but I think normalizing the path would be overkill It stinks, because for any given folder, I will have multiple sets of 'security levels' and overall the cardinality for that lookup will be pretty low (I hope I used that word correctly there). On paper, the system works well with a fairly large amount of data on a machine nowhere near dedicated to the database. What are your thoughts? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 1, 2012 Share Posted September 1, 2012 Can you give a concrete example? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.