Social networking DB schema?

Question

User

Groups
- Link
  - User1
  - User2
  - Time (or some other relevant link data)
- Posts

A user can only see a post if the user belongs to a link that belongs to the same group to which the post belongs.

I would like to optimize the database for querying all posts that a given user is allowed to see. What is the best way to structure the database to do this?

Thanks for the help!

EDIT: The above schema is a basic idea to get you started. Thanks again!

I don't quite get entity `link`. Is that read as "User1 has been friend of User2 since Time"? If that is the case, why does the link belong to a group? I understand it as a user belonging to a group. Let me know how you thought about this — Mosty Mostacho, Feb 06 '12 at 05:00
Sorry to take so long to get back to you on this. Here's what I was thinking: a user has a group in which are multiple other users. I need to store data about e.g. when one of those multiple users met the parent user, which is why I wanted to include the link object. The link is owned by both users involved in the link, so they can both see when they met each other and where. At the same time, if you are looking at that parent user again, you should be able to iterate through all groups, through all users within those groups (by means of the link objects) to get the posts belonging to each---- — Andrew, May 20 '12 at 18:57
----of those multiple users so you can generate an aggregate list of posts. This post query should be as minimally resource intensive as possible, given that it needs to be done often. — Andrew, May 20 '12 at 18:59

score 3 · Accepted Answer · edited May 23 '17 at 12:13

This is how I would model this:

Users(id, etc)

This is the user list. It contains only user data. The PK will be the id, so there will be an implicit index there

Groups(id, etc)

This is the group list. It contains only group data. The PK will be theid, so there will be an implicit index there

User_Groups(userId, groupId, etc)

This is the way to link users to groups. It needs to have the foreing keys to the users and groups and you can add any additional data to it, for instance, date when the user joined the group. The PK here will be a compound key between userId and groupId, so the index will be userId and groupId. You will also want to have an additional index in the groupId, because the "main order" will be given by userId, not by groupId

Posts(id, createdByUserId, belongsToGroupId, etc)

This is the lists of posts. Posts are created by only a user and belong to only one group. To display a post you'll join createdByUserId with Users table (to display the name of the user who created the post). Additionally, you'll want to check if the user who wants to see the post is actually allowed to see it. So, you'll go to the User_Groups table and use a WHERE clause similar to $currentUserId = users_groups.userId and $currentPostBelongsToGroupId = users_groups.groupId (OR you can join the table on post.belongsToGroupId = user_groups.groupId).

This is the general idea. Now, to focus on "optimize the database for querying all posts that a given user is allowed to see" you should basically do what I stated in the Posts entity description. As you're not looking for a particular post you'll have to join Posts with User_Groups (this join will use the groupId index created on the User_Groups entity) and then use the WHERE clause stated there too. That clause will use the compound primary key created on that entity. And that's it. It seems to be a fully indexed path.

Now, why didn't I add the link entity you've got on your question? Because I've not idea what it is and you didn't answer my comment on top asking what it was. If it was a link between 2 users then it shouldn't belong to a group because users belong to groups separately, not in pairs. Apart from that, if the link is what I assumed then it wouldn't affect at all the performance on how a user see his/her own allowed posts. Possibly related question to the link entity

Hope this helps or guide you to a better solution :)

Social networking DB schema?

1 Answers1