What is the most efficient way to specify user permissions in a database?

Question

I have an application that associates users with specific groups in a Facebook-style manner.

For example:

User A is associated with Group 1 and Group 2

User B is associated with Group 2 and Group 4

Users can create posts (small amounts of text) for each group that they belong to.

I have two types of pages: the home page for the logged-in user, and a page for each group. The user's home page shows posts from every other user that is in every group that the logged-in user belongs to. The group page shows all posts from every user that is in that group.

I'm not trying to replicate FaceBook here, but the closest parallel I can think of in functionality is how messages from one of your friends on FaceBook appear on your live feed page and also on the user's wall (profile page).

In my application, I have these three models (pseudocode):

class User():
    user_name
    first_name
    last_name

class Group():
    group_name

class Post():
    post_content

What is the most efficient way to associate these data in terms of database scalability and performance?

1) Associate each post to a user and a group. When a user views a group, select all posts from the Post table where Group ID = current group. When a user views their own home page, see what groups the user belongs to and find all other users that belong to that group. Then, pull all posts from those users.

2) Associate all posts with a user. When a user views their own home page, see what groups the user belongs to and find all other users that belong to that group. Then, pull all posts from those users. When viewing a group's page, find all users that belong to that group and then pull all posts associated with those users.

3) Create a join table that has PostID, UserID, and GroupID. When viewing a group, find all posts that have GroupID and pull those posts. When viewing the logged-in user's home page, find all groups the user belongs to and then find all users that belong to those groups and then pull all posts for those users.

All these solutions seem to require a lot of extra work to pull records for the user's home page. None of these options seem like the best solution. I'm sure someone has experience with this. I expect a large volume of posts, so I want the database to be scalable to support X number of records.

Is there a way to do this that makes more sense? How do the big companies manage to do the same thing and minimize their overhead?

in the 2) solution, the groups in which posts belong, can't be determined without user, is that correct in your business model? If users' belonging groups change, the posts' belonging groups change too. — Mike Lue

PerformanceDBA PerformanceDBA · Accepted Answer · 2010-12-11T06:16:08

You have not stated which dbms you have.

With an ISO/IEC/ANSI SQL database, which has a security (permissions) system built in, you need none of that, just set permissions using the SQL facility. It is internal and very fast.

If you want performance, scalability, and data integrity, you need to give up the idea for objects and classes, and learn a little about Relational databases. Further to that, if you model your data as a Relational database (not as object classes), 90% of your questions above will not exist.

If you do not understand what I am saying, read this recent post (starting from the 11 Dec 10 heading).

Is there a way to do this that makes more sense? How do the big companies manage to do the same thing and minimize their overhead?

Yes, absolutely. It does not make sense. What makes sense is to model the data properly, because a normalised model is much faster overall. This results in more, smaller tables, rather than fewer fat tables; the arithmetic and the laws of physics work. No data duplication means no Update Anomalies, which means transactions are smaller and less likely to block (you do want to allow multiple user concurrent access, right ?).

Besides, your coding will be hindered, beacuse you can't figure out how the data relates to other data. Throw your entities (the data contents of all your pages) up in your question (edit it and add to it), and we can have a go. I can see User,Group,Post, but what about Wall; any other furniture; Photos,Albums.

What is the most efficient way to specify user permissions in a database?

1 Answers