Table design advice

Question

I have a table users:

user_id - name

And those users can create an article and then share it with other members, table articles:

article_id - user_id - article_name

The question is the best way to share it... I'm thinking another table article_shares:

share_id - article_id - user_id

This would simply list all users with access to that aerticle and the creator would have access to be able to add or delete from that table for the article they created

So, when the article creator (user_id 123) looks at his articles he can see a list of all other users he has shared each article with

select as.user_id, a.article_name from article_shares as
join users u on u.user_id = as.user_id
join articles a on a.article_id = as.article_id where u.user_id = '123'

and a user (user_id 456) can see a list of articles they have been shared

select a.article_name from articles a
join article_shares as on as.article_id = a.article_id
where as.user_id = '456'

Does this seem logical? Am I on the right track?

Thanks for any help

Answer 1

You've got it right. If you're curious, you've made a junction table to create your many-to-many relationship between users and articles, and this is fairly standard.

You'll often see these types of tables named like ArticlesToUsers or something similar, and that will sometimes be the first way to tip you off that you're looking at a junction table. Of course, naming schemes are pretty subjective, so don't feel the need to change the name. article_shares seems like a good description to me.

As @MaxVT has demonstrated, you'll find that many developers won't put a surrogate key on a junction table like this, and would rather just use both columns as the primary key (article_id, user_id). The choice is obviously yours and may have more to do with staying consistent with the rest of the database tables, though you'll certainly see all permutations in the wild. In the event that you do keep your surrogate key, I'd recommend a unique constraint of article_id, user_id anyway to eliminate duplicates (why would an article need to be shared to a user twice?).