Using google doc, I am able to share my documents with others. I wonder how they implement it underlying in terms of DB design?

The simplest way I imagine is to use a joining table which keeps a many-to-many relationship between resource and accounts to share.

However, I wonder if there is any well-known pattern for this? If the account has hundred resources, then hundred joining tables seem not a scale way, as for each resource you have to write specific code. Is there any thing like RBAC(role based access control) for this sharing problem?


I don't have any insider knowledge of how Google Docs is implemented. However, if you look at things like Windows file permissions, this type of thing usually works on the these principles:

  1. Inheritance - To grant access to many objects with the same scope, grant the access at the highest level (e.g. folder, not file) and all children of the granted node get the same permission as the parent.

  2. Group and Individual Privilege - Rights can be granted to groups and to individuals. Individuals can belong to multiple groups and you might be in more than one that has access. Alternatively, access can also be granted to an individual.

  3. Explicit Grant - Assume access is denied unless there is an explicit grant in place. The explicit grant can be to the individual or to one or more groups that the individual is a part of. The grant can be to the specific resource or the resource can inherit the grant from an ancestor. However, no access is allowed unless there is a qualifying grant somehow.

By applying these three principles you can manage the amount of data that must be maintained to control access to resources.


See my answer to a similar question over on Stack Overflow. The basic idea is to map resources and principals into hierarchies or networks.

The resources (documents) will probably fit neatly in an un-level hierarchy of directories and documents. How you model the actor (user/group) depends on your business rules. Generally groups are one level deep, but users can be in multiple groups. This means you will probably want a sub-typing approach to principals such that they can be users or groups. You will have an intersection table (m:n) between principals to indicate membership.

To make the traversal of the resource hierarchy more efficient, you might consider one of the common relational hierarchy tricks. My usual favourite is "visitation numbers". See also my answer to this question for more about working with visitation numbers.

Consider the following (mostly logical) ERD:


Using this type of model, the query to find out if a user has access to a given document is a matter of joining Membership to Resource through Grant where the matched left and right are greater than or equal to the left and right of the resource in question. This gives you the answer based on group membership. To get direct individual grants you would do the same thing but leave out Membership and just use the ID from Individual to filter the Grants.

  • Thanks @Joel. Actually I know how to design such access-granting stuff, I just down know how to match the design to database tables. I want to see if any existing solution for database design for such kind of problem – zx_wing May 14 '13 at 20:13
  • @zx_wing - Have a look at my expanded answer to see if it helps with matching the design to tables. – Joel Brown May 14 '13 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.