2

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.

Thanks

  • ...designing a database... for what DBMS? – user2338816 Jun 26 '16 at 21:11
5

A is not good

a table for the keywords

ID  PK 
Keyword 

then have a join table

jobID       PK FK  
keywordID   PK FK

with FK to the two tables

| improve this answer | |
  • In the joined table, does a jobID with multiple keywords assigned have multiple rows? – tuskcode Jun 26 '16 at 5:42
  • yes a jobID with multiple keywords assigned have multiple rows – paparazzo Jun 26 '16 at 5:46
1

aha! I've recently been reading "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" by Bill Karwin. He talks about this specific antipattern in chapter 8. Trying to create X number of columns in the table is the incorrect solution.

The solution is to create a dependent table. You create a column in this table for the multivalued attribute. Define a foreign key for this dependent table that maps the values back to the parent table. Now, the multiple values are stored in the rows of the dependent table, instead of the columns in the parent table. Now you can create X number of rows for your keywords in the dependent table.

The code in MSSQL to create the table:

CREATE TABLE keyword (
keyword_id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
keyword NVARCHAR(30) NOT NULL,
fk_job_id INT NOT NULL REFERENCES job(id)
);

Inserting multiple values into a job:

INSERT INTO job ([id], [job number], [description], [client])
VALUES ('4', '215', 'Read Stack Overflow More', 'Everyone');

INSERT INTO keyword ([keyword], [fk_job_id])
VALUES ('Awesome', '4'),
('Spectacular', '4'),
('Spider-Man', '4');

Retrieving all records from jobs, and keywords:

SELECT *
FROM job
INNER JOIN keyword on job.id = keyword.fk_job_id;
| improve this answer | |
  • 1
    "number of keywords that are selected from a list" This does nothing to manage that list. – paparazzo Jun 26 '16 at 7:27
  • He said his question was to find the most efficient way to link the keywords to the jobs, and this solution does that. He just mentions that a list is used to insert the keywords into the table, I'm not sure there's a problem with that part of the process. – Luke Pafford Jun 26 '16 at 7:40
  • 1
    This is not the most efficient way to do that. Link the IDs as in my solution is more efficient. – paparazzo Jun 26 '16 at 7:43
  • It seems like the difference is my solution involves a one to many relationship and yours is a many to many. With the one to many it maps one job to many keywords. The drawback would be that if jobs constantly use repeated keywords then you could end up with a many table that has a bunch of job Ids with repeated keyword values. Yours uses a join table so that the value for a keyword will only be input once, but now you have a junction table involved. Is this a correct analysis? – Luke Pafford Jun 26 '16 at 8:03
  • They select from a list. Yes there will be repeats. That list need to be managed some where. The database would be a good spot. – paparazzo Jun 26 '16 at 8:14
0

Option A is really bad, option B is sufficient, option C overkill.

Why not A? Well, what if you need 6 keywords? Queries get ultra complicated when you have to compare all fields, ...

Why not C? It will only add extra complexity with another join query, but not really save you a lot of space, memory or whatever you try to save.

Space becomes cheaper with time, the key focus should here be the maintainability of your application. So make the life easier for whoever will work on the database in the future .. might be future you, he will thank you. ;-)

Use a new table if the linked data is a real object with different attributes, not just a mapping from string to integer. Or where you want to rename the keywords often. That's all not the case here I guess.

Since you are starting with DBs, if you really want to go with option C, it is not that bad as option A, so go for it, it's good training for when you really need the M:N-relation.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.