I am in the early stages of designing a database, and I have little (none) database design/planning experience. The database is part of a job tracking engine..
As jobs are created, they are assigned a job number, description, client, and a number of keywords that are selected from a list. My questions relates to choosing the most efficient (runtime speed) way of linking the selected keywords to the jobs.
I see a number of way to do this, some less conventional and maybe more dangerous than others...
Option A - limit the maximum number of keywords that can be assigned jobs to value 'N' (likely 5) and have the following table columns..
[id] [job number] [description] [client] [keyword 1] .. [keyword N] 1 123 zyx kk word test 2 183 tyx ff test - 3 214 xyx tt bleh -
Option B - Have a keyword table with a column for every keyword. The keywords in the entire database is administered manually, but there could be any arbitrary number of keywords, lets say X (likely in excess of 200). As job are created, a new row is added to the table and the job number, or id (link from another table) entered in each of the columns where the keyword has been selected. Eg
[word] [test] [bleh] ....... [keyword X-1] [keyword X] 1 - 1 1 - - 2 - - - - - 3 3 3
Option C - Similar to 'Option B', except keywords are given their own table. But that would just mean there are X number of tables in my database...
At this stage option A is the front runner, but I don't particularly like the hard limit on the number of keywords that can be assigned to a job. Can anyone suggest an alternative design, so that I don't have a ridiculous number of rows/tables, and so I don't have to limit the number of keywords assigned to a job.