Database Modeling, many to many relationship between two tables

Asked

Viewed 303 times

2

I have three websites that will share the same database. There are 3 tables: News, Photo Gallery, Blog. It will only be a management system for the 3 websites.

Example: the user will save a news that belongs only to 2 websites.

Question: How to best model the database?

Create a table and record for example the code of the website and the news to be recovered on the websites that can display them? Create only one field in each table and save which websites that the news belongs to? I thought it was this way, but how to record this data that can then do the search with SQL efficiently?

php mysql
  • 1

    Question based on opinions are not part of the scope of the site. Can you rephrase your question, please? For if not the answers will be based on opinions, and each will answer "I think this or that"

1 answer

2


I would do so, since the same news can be displayed on 2 or more websites:

Create a table Sites with the columns id (1, 2 and 3) and column url (website address, e.g.: www.meusite.com), here you enter the websites.

Then create a pivot table Sites_Noticias since the relation is N:N (many news for many websites), this is the table that will relate the Sites with the Noticias. In this table only stored two data, created a column id_noticia and id_site

So this way you already have news/news and the site/sites relecionados.

Now by ex:

Table Sites:

id | url

1 | www.meusite1.com

2 | www.meusite2.com

3 | www.meusite3.com

Table Sites_Noticias:

id_site | id_noticia

1 | 3

2 | 1

1 | 2

3 | 1

In this example we can see that the news whose id is 1 will appear on the site whose id is 2 (www.meusite2.com) and on the site whose id is 3 (www.meusite3.com). On site 1 (www.meusite1.com) will be published the news with id’s 3 and 2.

By pivot table (Sites_Noticias), depending on the site you are on, you can get the news (from the table Noticias) that should be shown. In this example if we have on site 1 we will fetch the news whose id’s are id’s 3 and 2.

SQL to get the news that is part of the site www.meusite1.com:

SELECT Noticias.* FROM Sites JOIN Sites_Noticias ON Sites.id = Sites_Noticias.id_site JOIN Noticias ON Noticias.id = Sites_Noticias.id_noticia WHERE Sites.url="www.meusite1.com";

I apologize for the poor formatting of the answer, I tried to be as clear as possible.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.