I have a table called posts which holds articles I post. It has a field I called post_group_ids.
I have a table called groups which holds named groups.
Rather than storing a comma delimited list of group_id in the posts.post_group_ids, is it better to instead create an interim table, maybe called post_groups that would have 3 columns (post_group_id, post_id and group_id) in which if I wanted to add groups 1, 2 and 3 to post 10, there would be 3 entries in this posts_groups table to reflect this?
The more I think about this, if the “post_groups” table is the best way, I wouldn’t need a PK of post_group_id. The post_id and group_id combinations should pretty much be unique. A PK would just increment for no particular reason.
the PK not only ensures uniqueness, but it also provides a composite index to retrieve all the groups for a specific post
to search in the opposite direction, i.e. to find all the posts for a specific group, a composite index is required with the two columns in the opposite order
but the PK index and the additionally declared index are covering indexes, which means that only the index is needed to resolve a query – for this table, the rows are never actually needed!!!