Database modeling, many-to-many relationship between two tables [closed]

2

I have 3 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 news that belonged only to 2 websites.

Question: What is the best way to model the database?

Create a table and record for example the code of the site and news to be retrieved on the sites that can display them? Create only one field in each table and save what sites does this news belong to? I thought of it that way, but how do I write this data so that I can then efficiently search SQL?

    
asked by anonymous 26.05.2016 / 13:43

1 answer

2

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

Create a Sites table with the columns id (1, 2 and 3) and column url (website address, eg: www.meusite.com), then you insert the sites immediately.

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

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

Now for ex:

Table Sites :

id | url

1 | www.meusite1.com

2 | www.meusite2.com

3 | www.meusite3.com

Table Sites_Noticias :

site_id | id_notice

1 | 3

2 | 1

1 | 2

3 | 1

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

For the pivot table ( Sites_Noticias ), depending on the site where you are, you can go get the news (the Noticias table) that should be displayed. In this example if we have in site 1 we will get the news whose id's are id's 3 and 2.

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

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 response, I tried to be as clear as possible.

    
26.05.2016 / 14:13