Table Design Guidelines

Question

I have table users:

user_id - name 

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

 article_id - user_id - article_name 

Question is the best way to share it ... I think another article_shares table:

 share_id - article_id - user_id 

This would simply list all users who have access to this aperture, and the creator would have access to be able to add or remove from this table for the created article

So, when the creator of the article (user_id 123) looks at his articles, he can see a list of all the other users to whom he 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 the user (user_id 456) can see the list of articles that they used.

 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 sound logical? Am I on the right track?

Thanks for any help

Answer 1

You are fine. If you're interested, you created a join table to create many-to-many relationships between users and articles , and this is pretty standard.

You will often see these types of tables called ArticlesToUsers or something similar, and this will sometimes be the first way to deny that you are looking at a connection table. Of course, naming schemes are quite subjective, so don't feel the need to change the name. article_shares seems like a good description to me.

As @MaxVT demonstrated, you will find that many developers will not put a surrogate key on a connection table like this, and most likely will use both columns as the primary key (article_id, user_id). The choice is obviously yours and may have more in common with the others in the database tables, although you will definitely see all the permutations in the wild. In case you save your surrogate key, I would recommend the unique restriction article_id, user_id in any case eliminate duplicates (why should the article be transferred to the user twice?).