What is the best way to store string of array inside a database column?
I have a tags
columns, where I want to store multiple tags.
Now I can think of two possible way to this but not sure which is really effective for large-size data.
1st
Directly store the data in an array of a row.
[ { id: 1, name: 'action' }, { id: 2, name: 'classic' }, { id: 3, 'science fiction' } ]
If I want to add the data I push at the end of the array and for remove I can search it by ID
.
2st
Create a separate tags table.
| id | name | post_id | tag_id |
| --- | --- | --- | 2 |
| 1 | 'action' | 342342 | 34 |
| 2 | 'classic' | 32424453 | 432 |
| 3 | 'science fiction' | 32424453 | 34 |
Based on the post_id
in the tags table I can get all the tags for that specific post.
NOTE: These are not the actual tags but the reference where; which post is having which tag.
I would like to hear your feedback on these two ways, if you have another way to deal with array data type, please let me know in the comment section below.
Top comments (21)
The post_tags table contains the foreign keys of tags and posts.
Something like this (:
To retrieve a post and all its tags, you need to JOIN the tables in your queries:
This answer is adapted from How to store arrays in MySQL from StackOverflow.
This is the best solution on the topic. +1
Thank you for sharing the code sample.
But, I want to share multiple tags for single post.
On using the
INNER JOIN
clause it doesn't get the data if specific post have multipletag_id
it only return the data for 3rd row according to the above image.
Can you see the mistake here?
You need to do the JOIN with the post_tags table (with both references, posts and tags) to work.
Yeah I know that,
I tried with the both with and without
post_tag
table, it was return 0 rows.Depends on what tags mean in your app, as in both examples have ids I guess they are part of your domain model. You can have a table which lists all available tags and another one with foreign keys to both posts and tags to list which posts have which tags.
As I have mentioned in the article. A post can have multiple tags.
So yeah, having a many to many table is one solution. But you can also store them as an array or as JSONB in PostgreSQL. It depends if tags themselves are an entity in your app you want to attach metadata to or are just, well, an array of names you might want to query on (for example to give you the list of posts that have a specific tag)
Yes, tags itself have a separate entity in the app, to explain it in simpler words, this is how the data output I am expecting.
I have seen it stored like this (which is actually Anki)
But if I had to do it more complex, like more fields, and rename-able, I would definitely do it the 2st way.
I wouldn't rely on JSON Object, if I can avoid.
Hmm, did you discard creating another table? What was the reason behind?
What do you mean by creating another table?
In the second option I am creating another table name
tags
to store the connection between the posts and tags.I'm sorry, I totally missed. I just though that you would storage the array in a varchar (or any lob type) using JSON o separating it with |.
I would go for this three tables: posts, tags and post_tags (Or any naming you like).
posts --> All the posts
tags --> A master table with all the tags
post_tags --> A table with a post_id and tag_id to do the many to many relationship.
You mean a single row of
post_tags
can contain multipletags_id
column.I mean something like:
posts
tags
post_tags
Personally I would use the second approach but make a link table which can group all the tags together via a foreign key.
Cheers, Scott
I didn't understood the part about making a link table?
Could you please elaborate that?
So I would have two tables. The first one would contain the a primary key of post_id and the name or that post.
The second table would contain all the individual tags linked to that one post. You could then make a foreign key link to the first table.
Hope that makes sense. But think you’ve done this already in your second approach.
Good job
Right, but using the concept of foreign key we can only link single tab to the post table, not an array of tags. Right?
I might be wrong too, IDK
Some comments may only be visible to logged-in visitors. Sign in to view all comments.