Should I do de-normalization in this case?

Question

Here is the situation:

[Job] belongs to one [Job Category], which [Job Category] is belong to one [Industry]

So, my initial design is like that: A Job have a fk to find the Job Category, and a Job Category have a fk to reference to Industry. But the problem is when I want to know the job is belong to which industry, I need to find the job category, and based on the job category id, to look back the industry table. So, I am thinking doing de-normalization.

But I have two approach to do so, I am thinking which is a better one. First, records all stuffs, including the Job Category, Industry in the same place(the Job table).

Another approach is Job still reference to Job Category, but Job Category have a field to store the industry information.

Which approach you think it is better? Thank you.

Answer 1

Your second approach is good. The problem you describe, getting an industry for a given job, is actually not a problem. See the following structure:

Table jobs
ID PK
REF_CATEGORY FK

Table jobcategories
ID PK
REF_INDUSTRY FK

Table industries
ID PK

Now, to get the industry for a given job:

SELECT j.ID, i.ID
FROM jobs j
JOIN jobcategories c ON j.REF_CATEGORY = c.ID
JOIN industries i ON c.REF_INDUSTRY = i.ID

There are good reasons to sometimes denormalize, but in this case you shouldn't. If you put the JOB, JOBCATEGORY and INDUSTRY in one table, you risk having a jobcategory belonging to multiple industries, thus breaking your 1-n relation.

Answer 2

But this kind of referential relations are always there. If you are using the Industry table and the job category table only for the Job details, then you can think of de-normalisation. But if these tables are also connected to some other data/tables, the normalisation scheme should be reviewed. De-normalisation should not be done in isolation. And, I don't feel you are having a very high overhead to connect three table on RDBMS.