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:
image
id | src
category
id | name
news
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?
Best Answer
To delete a news-category pair in schema B, you have to do either a
DELETE
or anUPDATE
depending on whether the relevant row hasid_gallery
specified or not. How about inserts, are you going to justINSERT
or check first for rows with nulls toUPDATE
?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.