SQLite set multiple values to specific foreign key

Question

I have two tables in SQLite, article and keyword table. In the article table, I have some columns like article name, article year ... and also article keyword. I set the article keyword column as a foreign key and connect it to the keyword table.

But there is more than one keyword from the keyword table belonging to a specific article.

For example in the keyword table:

key_id    keyword 
-------------------
  1       xxx 
  2       yyy
  3       zzz

and in the article table and keyword column for specific row, I need xxx and yyy. So I should connect it by key_id 1 and 2. how I can do this?

Set multiple foreign key from specific table.

Answer 1

In relational database system, you normally need a third table to model the one-to-many relationship between an article and its keywords. You can name your table article_keyword, and it shall have two columns, article_id and keyword_id:

article_id  keyword_id
1           1
1           2
1           3
2           2
2           3
...         ...