CODEWITHSUNDEEP
postgresql
Zoltan King
Zoltan King

Reputation: 2262

SQL table design for tags. Each user has its own tags

I have three tables, one for storing articles, one for all tags, and one for the relation between the two:

[table: article]
------------------------------
id        title       content
------------------------------
1         title       content
2         title       content
3         title       content


[table: tag]
----------------
id        name
----------------
1         health
2         travel
3         sports


[table: article2tag]
------------------------
article_id        tag_id
------------------------
1                 1
1                 2
1                 3

The first article (article_id: 1) has all the tags attached to it (tag_id 1, tag_id 2, and tag_id 3).

I only have one user on the website for now, but I'd like to create more users and each user to have its own tags which are added by them. Do I need to add a user_id to the tags table to keep track which tag belongs to which user in that case?

[table: tag]
-------------------------------
id        name         user_id
-------------------------------
1         health       1
2         travel       1
3         sports       1
4         sports       2
5         business     2

Thank you

Upvotes: 0

Views: 1084

Answers (2)

forpas
forpas

Reputation: 164069

Adding a column user_id to the table tag is the correct thing to do, like the sample data you provided, although the column id is needed only as a reference if you define the combination of name and user_id as the primary key.
This means of course that the column name would contain duplicates, because more than 1 users may define the tag sports or any other. But this is fine.
So in this table:

[table: tag]
-------------------------------
id        name         user_id
-------------------------------
1         health       1
2         travel       1
3         sports       1
4         sports       2
5         business     2

sports is defined twice, once for each of the 2 users.
If user_id = 1 decides to rename the tag say to water sports then only 1 change will be made to the table by updating the row with user_id = 1 and name = 'sports'.
If a user adds or deletes a tag then all you have to do is add a new row to the table or delete a row from the table.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Yes, you should have a junction table which stores the relationships between users and tags, but that table should not have the tag name, only the tag ID:

user_tags

user_id | tag_id
1       | 1
1       | 2
1       | 3
2       | 4
2       | 5

The reason why the tag name should not appear in this junction table is that a given tag could be associated with more than one user. In that case, repeating the tag name many times would be redundant information, and wasted storage space. Instead, keep your current tag table as is, but remove the user_id column.

Upvotes: 2

Related Questions