How to relate keywords to records - Many to Many

Question

Hi All,

I am looking for suggestions on database design for a sample jobs listing application. I have many jobs that I would like to associate various keywords with. Each job can have multiple keywords. I would like to store the keywords in a seperate table instead of in a field within the Job table so as to avoid mispellings in keywords.

What is the best way to relate keywords to the jobs? I was thinking of using an intermediary table that would have a many to many relationship linking keywords to jobs.

Is this the best way to go or should I just have a field in the Job table that contains multiple keywords? Thanks for any suggestions.

Answer 1

Your suggestion to use an intermediary table is probably the best and most common way to solve this problem. It also conform the the third normal form. (Although, which in itself, is not always an useful goal)

Answer 2

Using an itermediary table for many-to-many relationships is the normal way to solve the problem.

The other alternative you are mentioning; to put multiple keywords in one field breaks database normalization which one should keep to, unless having good reasons not to. See more at Wikipedia