Foreign key shortcuts in table

Question

Imagine a schema as such.

NOTE TABLE:            NoteID, Note, DetailedTaskID, .....

DETAILED TASK TABLE:   DetailedTaskID, WorkOrderID, .....

WORKORDER TABLE:       WorkOrderID, ProjectID, .....

PROJECT TABLE:         ProjectID, .....

Now with this schema lets say I want to retrieve all notes that are associated to a specific project I end up with quite a number of joins.

IE: Note JOIN DetailedTask JOIN WorkOrder JOIN Project

So my question is this, when (if ever) is it appropriate to add a "shortcut" column for a table (in this case ProjectID)?

So basically changing the note table to this: NoteID, Note, DetailedTaskID, ProjectID

Cerated

Answer 1

Short answer: Never, ever, ever.

Longer answer: Only when:

1. You've determined that the performance of the JOINs is unacceptable (which is rarely true).

2. You've genuinely exhausted all less dangerous alternatives.

3. You are willing to absorb the extra work involved in keeping the redundant, de-normalized information in sync

4. You are willing to accept the fact that it then becomes technically possible for your database to return incorrect results if you ever fail to keep things synced up.

Condensate

Could I just confirm? When you say "less dangerous" I assume you mean a scenario where a bug could cause one foreign key to point to one project and one pointing to another, right? – Cerated

Just so. In this case an oversight in your code (or someone else's code, added later) might update the foreign keys in one table and not the other. Or, it might update only some of the de-normalized foreign keys values in rows in the second table, but not others. Or it might update too many rows in the second table. And you may not see these errors right away, or consistently, if sometimes you are obtaining the foreign key values from the normalized table and sometimes from the denormalized table. It can be done, but make sure you have a real need to do it first. – Condensate

Answer 2

What you're talking about is called a foreign key relationship. It's the basis for Data Normalization. In short, you would add a foreign key relationship (ProjectID) to your Note table if a Note (i.e. NoteID) belongs to a Project.

The benefits of doing this are so that you can query this relational data, like this:

select
  Note.*,
  Project.*
from Note
left join Project
on Note.ProjectId = Project.ProjectId

That query would yield all notes, and project data (if it is part of a project) that is related to it.

Choroiditis