Need advise on MSSQL best practice

Databases
1

Using MSSQL2005 on Win2K3/8

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?

2

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.

3

This approach seems fine to me. I guess you need to create a composite key: post_id + group_id

4

Composite key? I’m actually unfamiliar with the concept or how to implement.

Is this where you just ensure that the 2 fields cannot allow for a duplicate record with the same values?

5

it’s actually very simple…

CREATE TABLE post_groups 
( post_id INTEGER NOT NULL
, group_id INTEGER NOT NULL 
, [COLOR="blue"]PRIMARY KEY ( post_id, group_id )[/COLOR]
, INDEX group_posts ( group_id, post_id )
, FOREIGN KEY ( post_id ) REFERENCES posts ( id )
, FOREIGN KEY ( group_id ) REFERENCES groups ( id )
);
6

Composite keys. Nice. Thanks guys!

7

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!!!