CODEWITHSUNDEEP
mysqlsql
user2997497
user2997497

Reputation: 160

SQL: Seeking an advise for tables structure etc

First what are conditions. I have people belonging to "small" group. (which in other words means every one has "small_group_id". Then "small" groups form "big" groups (which in other words means "small_groups" may or not have "big_group_id" depending if small group belongs to bigger ot not).

I want to create a table structure (that would be used by PHP) for keeping and displaying two following things:

Basically the challenge for me is how to design and later work with visibility of those private messages. My first though was table like: msgID, msgBody, small_groups_list, big_group_list, authorID So I store e.g. in 'small_groups_id' something like 'id_1; id_4; id_10', etc and similar for big groups. But then I'm not sure how do I do search through such stored lists when e.g. person belonging to small_group_id = 10 supposed to see that mesage. Also what should be the columns small_groups_list and big_group_list defenitions/types.

Perhaps there is better way to store such things and using them as well? That is why I'm here. What would be better practices for such requirements?

(it is going to be implemented on mySQL)

Thank you in advance.

[edit] I'm pretty unexperienced in SQL and DB things. Please take that into account when answering.

Upvotes: 1

Views: 48

Answers (1)

asthasr
asthasr

Reputation: 9417

First: Don't denormalize your data with "array" columns. That makes it a horror to query, and even worse to update.

Instead, you need two separate tables: small_group_visibility and big_group_visibility. Each of these two tables will consist of msgID and groupID. Basically, it's a many-to-many relationship that's pointing out to both the group and the message it is concerned with.

This is a pretty common database pattern.

To query for messages to be displayed, imagine that we have a user whose small groups are (1, 2, 3) and whose large groups are (10, 20).

SELECT DISTINCT msgID, msgSubject, msgBody -- and so on
FROM messages m
LEFT JOIN small_group_visibility sg
  ON sg.msg_id = m.msg_id
LEFT JOIN big_group_visibility bg
  ON bg.msg_id = m.msg_id
WHERE
  sg.group_id IN (1, 2, 3) OR
  bg.group_id IN (10, 20);

Upvotes: 2

Related Questions