How should I structure this group/role schema?

I'm writing an application which has certain features that are similar to Google Circle/FB friend list.

1. A user can put people they know into group (family, coworker, friends, etc...) (for now groups cannot be nested)
2. A user can send messages to group(s), set privacy settings per group, etc
3. When a post is shared within a group, the users of those groups can comment and see the others' comment regardless of the relationship they have with the others (within that group)

For now, we are using relational datab开发者_运维知识库ase (mysql) because of some limitation with time and resource. In any case, I'm trying to find the best way to structure our database to balance performance and clarity. Here is what we currently have:

users:
  user_id
  default_group_id
  friend_group_id

groups:
  group_id

groups_to_users:
  user_id
  group_id

messages:
  message_id

messages_to_groups:
  message_id
  group_id

galleries_to_groups:
  gallery_id
  group_id

When a user is first created, he/she will have 2 base groups:

1. the default group which will contain only that single user
2. the friend group which will contain everyone he/she is friend with

We will simply use group_id to determine the "permission" instead of using user_id. That way we can skip the complexity of querying 2 tables.

At the same time, with the structure above we also hit a roadblock with querying all the messages a user received since we may have to query at least 100 groups if this user has 100 friends. So right now we get around this by this rather hacky method:

if a user sends a message to a group, then we go through the list of member in this group and save a record for each user (message_id, (default_)group_id). The problem is that, if this group has 1000+ members then we will have to insert 1000+ records for each new message sent to this group, and also when this user makes any change to the member of the group we will also have to update a massive number of records.

I wonder if there is any better way to structure our database to improve performance?

Your "hacky method" defeats the purpose of having groups since you are in effect writing links to individuals instead of using their group memberships to rationalize your transactions (i.e. messages). If your concern is performance then you are probably not going to get a huge boost for your reads by multiplying your writes by a factor of 100 or 1000.

I think you should stick with the original design and make sure that your tables are properly indexed so that the DBMS can do what it is built to do - which is join data sets quickly and efficiently.

If you design your tables to have the right kind of primary and foreign keys and if you design your queries so that they take advantage of the PK/FK indexes then that is how you will optimize your performance.

Tree structure is the suitable fit to represent this kind of hierarchical data

For example { <user> <guid>uid1</guid> <message>msgid2</message> </user> <user> <guid>uid2</guid> </user> <group> <guid>groupid1</guid> <member>uid1</member> </group> <group> <guid>groupid2</guid> <member>uid1</member> <member>uid2</member> <message>msgid1</message> </group> } So that the data model can be flexible to find

• target message to individual user or a group
• list of messages a particular user is the cumulative of messages addressed to the list of groups the user is member of and the messages addressed to the user directly