I have a database to store the news of my website. The news entity type is associated with two different ones; images (which from the point of view of the app users are grouped in a gallery) and categories.

To represent this situation this, I have some tables like this:

id | src

id | name

id | title | text | author | ...

It is important to mention that each news may have multiple images and belong to multiple categories. So, to create the relationship between images, categories and news, I'll use another table, but my question is:

Should I have (a) two tables (one for image rows and other for category rows) or (b) just one with multiple columns? For example:

(a) Two distinct tables, one called news_relationship_category, with the column headings

id | id_news | id_category

and another one, named news_relationship_gallery set up as follows

 id | id_news | id_gallery

(b) One single table, entitled news_relationships with the headings

id_news | id_category | id_gallery

and INSERT INTO it rows with some columns that accept NULL marks, for instance:

10 | NULL | 3
10 | 27   | NULL

These are simple examples, but imagine if I have an e-commerce table, with products involved in multiple relationships. What is the best way to approach this case?

  • 1
    If your news can have 3 categories and 4 images, use the second approach. Is the one that best represents your situation. And don't use id. Use id_news, id_gallery and id_category all the time. It's far more clear. Your new_relation_category does not need an id for itself: it's just (id_news, id_category).
    – joanolo
    Commented Feb 2, 2017 at 23:30
  • I forgot to mention that each news may have multiple images and multiple categories. So you mean I should have one relational table for each area? One for gallery and one for category? @joanolo Commented Feb 2, 2017 at 23:32
  • That's it: one for gallery and one for category. (This, obviously, assumes that galleries and categories are, in principle, not directly related; meaning you don't have one gallery per category).
    – joanolo
    Commented Feb 2, 2017 at 23:34
  • 1
    And you may want a category:gallery relationship? Hence, a 3rd table?
    – Rick James
    Commented Feb 3, 2017 at 1:18
  • Sketch out the SELECTs you will be doing -- they may provide your answer !
    – Rick James
    Commented Feb 3, 2017 at 1:19

2 Answers 2


To delete a news-category pair in schema B, you have to do either a DELETE or an UPDATE depending on whether the relevant row has id_gallery specified or not. How about inserts, are you going to just INSERT or check first for rows with nulls to UPDATE?

So, I strongly recommend option A. However, I would drop the surrogate id columns and use (id_news, id_category) and (id_news, id_gallery) as PK.


In your case, it appears that you will need five tables to do this:

images ← newsimages ← news → newscategories → categories

Here the news table is linked to the categories via an associative table newscategories which is the only way to get a many-to-many relationship between news and categories.

enter image description here

The newscategories associative table simply contains a collection of unique associations between news and categories. You can get this by combining the two foreign keys as a single primary key:

CREATE TABLE newscategories (
    newsid INT REFERENCES news(id),
    categoryid INT REFERENCES categories(id),
    PRIMARY KEY(newsid,categoryid)

You would do exactly the same for the images table.

If you want to get a list of, say, categories, you can use a combination of group_concat and either a sub query or a common table expression if your MariaDB/MySQL is recent enough:

        c.name as category
    FROM news AS n
    JOIN newscategories AS nc ON n.id=nc.newsid
    JOIN categories AS c on nc.categoriesid=c.id
) AS cte
GROUP BY id,title;

You can see an example at http://sqlfiddle.com/#!9/219526/2 . However:

  • The last example doesn’t work properly, but it does on a real server
  • It would have been easier if you could use Common Table Expressions, but SQLFiddle doesn’t appear to support more recent versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.