Model tree in relational database with property inheritance

From: <yashbagadia22_at_gmail.com>
Date: Sat, 5 Jan 2019 02:30:00 -0800 (PST)
Message-ID: <14c13816-ade9-473e-b763-fc1448afb53d_at_googlegroups.com>



Hi Everybody,
[Quoted] [Quoted] 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. Received on Sat Jan 05 2019 - 11:30:00 CET

Original text of this message