How to select all articles and their similar articles from MySQL?

What are good ways to store Authors, Articles, Magazines relationship in Relational Database?

• I have following entities: Author: Id, Name Article: Id, Title, Contents, Magazine_Id AuthorArticle: Author_Id, Article_Id Magazine: Id, Title Author - Article is Many to Many. What is the most efficient way to get the list of all the Magazines in which the Author's articles were published knowing the Author.Id. Are there better design options than this?? Currently I first select all the Articles and the Select DISTINCT Magazine_ID from them.

• Answer:

  The table structures you have mentioned should work pretty well. For purposes of clarity and consistency, let's rename the columns. AUTHOR (author_id, author_name) MAGAZINE (magazine_id, magazine_name) ARTICLE (article_id, article_title, contents, magazine_id) AUTHOR_ARTICLE (author_id, article_id) As for the query, since you want to fetch data for a single author at a given time, keep the query on Author table separate (as a sub-query): SELECT DISTINCT mag.magazine_title FROM Magazine mag,      Article art,      Author_Article aa WHERE mag.magazine_id = art.magazine_id AND   art.article_id = aa.article_id AND   aa.author_id = (SELECT auth.author_id                       FROM Author auth                       WHERE auth.author_id = '<enter id here>')