Model tree in relational database with property inheritance

[yashba...@gmail.com]

Hi Everybody,
I have to implement a directory kind of structure with relational databases. Just like normal file system the directories and the files inside them will have permissions associated with them.
Moreover, permissions will be inherited from the parent and priority will be given to permission defined on the file/directory itself or the nearest ancestor. Also, if permission is defined for some parent directory then it will not be copied into database again for each child unless we want to override parent permission for specific child.

Priority of Operations in order of frequency:

1. Get the tree structure of files/folders for which user has access.
2. Check if user has access to a particular file. (In this case, permission has to be taken from file id itself if specified, otherwise we have to search for nearest ancestor).
3. Add a new file.
4. Add a new folder.

I was banging my head with Adjacency lists, Materialized paths and Nested sets.
Unable to come to a good solution which optimizes both read and write.

[edpr...@gmail.com]

On Saturday, January 5, 2019 at 5:30:01 AM UTC-5, yashba...@gmail.com wrote:
> Hi Everybody,
> I have to implement a directory kind of structure with relational databases.

WHY?

Is this for a class?
If this is a class assignment, we'll try to guide you,
but not provide a solution.
I can see that this might be useful (I have an application in mind),
but it would help if you describe your application. This might be the
wrong solution to your application.

Have you tried writing an Entity Relationship Diagram to model this
before trying to implement it?

What is the initial input? Are you sucking in a real file system?
Or building this from the input calls (3 and 4 in the list of operations)?


Ed

[-CELKO-]

I designed a system so that meets your description years ago using a nested set model. It was for access control, and controlled permissions on software. Basically the higher levels in the tree, you were given access to software packages that apply to your department, project team or whatever organizational aggregate was appropriate. So at one level. Maybe all employees had access to the company email, while the guys in the engineering department had access to the daily Dilbert cartoon strips, but as you went down to a particular project team they could only see the software that belong to that project. The only bad part of it was there was no good way to exclude people except one employee at a time. The nodes in the tree were basically a list of software, with flags as to what level of access they had.

It wasn't really that hard to design once we got the basic principle.

[Derek Ignatius Asirvadem]

This thread is nine months old, so for the record, for other readers.

> On Saturday, 5 January 2019 21:30:01 UTC+11, yashba...@gmail.com wrote:
>
> 1. Get the tree structure of files/folders for which user has access.

Easy. The Relational Model is founded on the Hierarchical paradigm, tgus it handles hierarchies beautifully. There are three types of hierarchies, the one you need is the simplest: each item has just one parent.

https://www.softwaregems.com.au/Documents/Student%20Resolutions/Andrew%20DM.pdf

For readers who are unfamiliar with the Relational Modelling Standard:
https://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Introduction.pdf

For a full Question and Answer interaction:
https://stackoverflow.com/a/4275227/484814

> 2. Check if user has access to a particular file. (In this case, permission has to be taken from file id itself if specified, otherwise we have to search for nearest ancestor).

Just add a table for Permission, and an FK to the given table. Use the concept of SQL Roles, so that permissions are set for a Role, not for each User.

At some point you will have to code a Function (some idiots call it UDF, idiots because everything that you place in the database is UD) to obtain the directory tree, ie. a concatenated Path, as opposed to individual Nodes.
https://www.softwaregems.com.au/Documents/Tutorial/Recursion/Hierarchy%20Inline.pdf

For that, the Function has to be Recursive. All genuine SQL platforms support recursion, the freeware/shareware/vapourware/noware does not support recursion. Such platforms have bells and whistles but not the basic requirements of SQL (hence their use of the term is fraudulent), and no Server Architecture whatsoever.

In that Function, accumulate or update the Permission as you navigate the tree, such that at the end, the Permission will be that of the last ancestor.

> I was banging my head with Adjacency lists, Materialized paths and Nested sets.

Yes. putrid filth, from people who are evidently clueless about the RM, and SQL. Some of them are implementation poured in concrete, if a Node changes, the entire tree has to be updated. Some have masses of duplicates.

Cheers
Derek

[Derek Ignatius Asirvadem]

> On Monday, 7 January 2019 05:52:44 UTC+11, -CELKO- wrote:

Aren't you the guy that wrote the book titled /SQL for Dummies/ or similar ?

Aren't you the guy that proposes Adjacency lists and Nested sets and other such primitive and horrendous anti-Relational methods, instead of the simple Relational Method ?

> The only bad part of it was there was no good way to exclude people except one employee at a time.

You need to use SQL Roles. Instead of relating the asset to a list of Users, relate it to a Role. Suers are then GRANTED Roles.

Cheers
Derek