Should I use a foreign key or string value from an external table?

Question

Suppose you have a staff table and a job list table. Each employee should have one job in the table. I usually gave each job an identifier and referred to it as a foreign key in the employee table. One of my colleagues, however, suggested that we use the job list table as a dictionary and that before inserting / updating we check the “job type” in the table and then insert the job type as a row into the employee table.

As far as i can see

Pros:

Faster choice (although connecting on the primary key should make a marginal difference)

Against:

Slower to select employees by type of work
Difficult to change work Type

Which of these approaches is best?

Answer 1

I'm not sure I understand why there is a conflict. Do you know that keys, both primary and foreign, can just as easily be string types as number types? You can take advantage of both systems by making the job type field the primary key in the job type table and the foreign key in the employee table.

The advantage of this approach is that you do not need to join the job type table back to the employee table to get a descriptive name for the job type, while you are still using the database to provide important referential integrity to your database design.

The disadvantage is that if you have a lot of employees, you will use a little more resources for storage, and if you change the job description, you will need to cascade the changes in the employee table.

While integers are usually used as primary keys (and for some frameworks this is required), there is no rule that says it should be that way.

Answer 2

If this makes it difficult to administer job types (which I assume is what you or someone else will do), would it really be worth the slight increase in productivity? It seems like this might be too complicated for a small reward. I would say that it is best to stick with a foreign key for simplicity.