Foreign key shortcuts in table

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 joi开发者_运维问答ns.

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

---

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.

---

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.