I want to create a scheme for storing and accessing viewing permissions in a database for a social networking site. This question is similar to other questions here, for example how are viewing permissions usually implemented in a relational database? and What is the most efficient way to specify user permissions in a database?, but there's primarily one significant difference: I'm not forced to use a relational database like these people are, and could look around for other solutions. I can also use a cache for storing any kind of pre-calculated structure I want even if I end up choosing a relational solution, as long as updates can be handled in a reasonable way.
Several items should share similar access right settings (photos, status updates etc.), and these are the primary settings I want to handle right now:
- Private
- Public
- Available to my friends
- Available to friends of my friends
- Available my groups
The first two are of course simple, but the last two seem complex. The current implementation uses a relational database with flags for each item, and I can't think of ways to solve it without subqueries or multiple queries. I could, of course, use a many-to-many table combining items with groups or even users, but I fear that it would cause a huge amount of updates every time anything changes.
Right now, getting the actual content is a breeze, but I spend an insane amount of time on checking the permissions. Also, I can get a bunch of photos in a single request, but I need to check the viewing permissions individually since different rules may apply. This solution doesn't seem to scale at all.
I'd also like to handle exceptions for particular users or groups for a particular item. On top of this, there might be some cases that should be treated differently depending on the item class. For example, you might want to give users who are tagged in a photo the right to view that photo (for example in order to counteract cyberbullying).
- How do others solve this?
- Are there clever ways to make this work with a relational database?
- Can I do this in a DRY way, if many classes of items should be treated in similar ways?
- Are there other kinds of databases that I should look into, like graph databases or something? Any suggestions on specific software in that case?
