Database Design: Multiple join tables or one table with identifying 'table' column

Question

I'm designing a database where any content can be tagged and I'm likely to want to be able to select all content with a specific tag.

I'm struggling with the following two options and would appreciate some advice. If there's a better way please let me know.

Option A
Multiple 'many to many' join tables.

tag:
  id
  tag

media:
  id
  title
  src
  creation

media_tags:
  id
  media_id
  tag_id

article:
  id
  title
  content
  creation

article_tags:
  id
  article_id
  tag_id

Options B
A single 'tag reference' table, which uses a 'table' column to identify which table to join to.

tag:
  id
  tag

tag_reference:
  id
  row_id
  tag_id
  table

media:
  id
  title
  src
  creation

article:
  id
  title
  content
  creation

From a maintenance point of view option B seems favorable but considering the SQL query to select all content and don't think it's possible without multiple queries.

I'd go with the first one. Storing table names as a column is not such a good idea; imho. — hjpotter92, Oct 16 '12 at 08:21
Have a look at this: http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database. Your question is a specific instance of the general problem of inheritance... — Neville Kuyt, Oct 16 '12 at 08:36

score 2 · Accepted Answer · answered Oct 16 '12 at 08:22

2

When using Option B, you can't set up foreign keys to the other tables. Thus I would go with Option A and one table for each m:n relation.

"From a maintenance point of view option B" – is a nightmare. What happens if you delete an article? All the rows with that row_id will persist in tag_reference table. You always need to update those entries manually.

answered Oct 16 '12 at 08:22

feeela

29,399
7
59
71

Thanks for your answer, it makes complete sense. Option A is the winner – Peter Hough Oct 16 '12 at 09:07

score 1 · Answer 2 · answered Oct 16 '12 at 08:27

1

Option B contains a multivalued dependency - and as such is breach of 4th normal form. I much prefer Option A

answered Oct 16 '12 at 08:27

podiluska

50,950
7
98
104

John Woo · Answer 3 · 2012-10-16T08:24:16.950

0

Actually, It depends on every sql developer. But I prefer Option A since you can easily know that a certain column in a table is a foreign key (assuming it's true) of the other table.

Option B is somewhat bad design because storing table names in a column is bad idea. You can spend more IF or CASE here.

edited Oct 16 '12 at 08:24

answered Oct 16 '12 at 08:18

John Woo

258,903
69
498
492

score 0 · Answer 4 · answered Oct 16 '12 at 08:26

0

The second option pretty much prevents you using any JOINs for efficient SQL, forcing you into using slow multiple selects.

So I would say the first option is far preferable.

answered Oct 16 '12 at 08:26

Kickstart

21,403
2
21
33

Of course is it still possible to join the tables – even when using such a layout as in the **Option B**. Maybe it's not that efficient and you have to pass the table name as search parameter every time. – feeela Oct 16 '12 at 09:19
Only if you know in advance which table to join against, or join against all possible tables in the SQL. – Kickstart Oct 16 '12 at 09:59

Database Design: Multiple join tables or one table with identifying 'table' column

4 Answers4