CODEWITHSUNDEEP
postgresqldatabase-design
malcoauri
malcoauri

Reputation: 12189

How to store keywords for posts in database

I have posts table in PostgreSQL with title, content and created columns. I want to add some keywords for each post, but I have some doubts about how I should store them. The ways I'm considering now:

  1. keywords table with post_id foreign key
  2. keywords column at posts table with array of keywords (for example, "england football liverpool").

After creating UI I'm going to install ElasticSearch on my server and add ability to search posts by keyword on my site. I need to know what a way do you prefer to make search faster? Thanks in advance!

Upvotes: 0

Views: 573

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Neither of the options you suggested sounds good to me.
I would go for a many to many relationship, as I suspect a lot of posts will probably share a lot of keywords.
Therefor, I suggest a Keywords table with Keyword_id and Keyword_name and a PostToKeyword table with PostToKeyword_Post_Id and PostToKeyword_Keyword_Id.

Upvotes: 1

Related Questions