Whether to use additional column or use existing

Hello,
I am tracking when call center cases (fact_case) are re-assigned from one person to another using that persons id. Now I find that cases can leave the call center for in-person visits (different mode). I want to use the same column, fk_reassigned_to_id when a case switches modes. Is that an ok solution or should I create a new column something like fk_mode_switch_id?

My main concern with using existing column is that a call center re-assignment is a different process than the mode switch. However, in using different column, I thought there would be difficulty reporting on the complete history of the case, from call center assignment, to re-assignment, to mode switch and possible further re-assignments.

Please let me know your thoughts. thanks

Sounds like you need a new fact table instead of adding additional FK's. How many times can it get reassigned, once, twice, three times? A reassigned transaction fact would easily handle counting all the variations you have.

-

Thanks Boxes and Lines! Yes, the case can get re-assigned countless times, and can go from call center to visit, and back to call center, or even Internet and email. However, could you explain your reasoning? I think my concern with creating another fact table is that records would be entered into both fact tables; fact_case to show latest status of a case and "fact_assignment" to show assignments/re-assignments.

Status can generally be tracked via a dimension. The exception is when the status change is an important enough of an event to warrant its own fact table. Otherwise, a type 2 on status can always give you the current and historical status.

BoxesAndLines wrote:Status can generally be tracked via a dimension. The exception is when the status change is an important enough of an event to warrant its own fact table. Otherwise, a type 2 on status can always give you the current and historical status.

I do track status via Dim_status, and have a status of re-assigned as well. However, I use the re_assigned_to_id column to track who has the case over time, so I can report on the history of the case. If I create a new fact table, then wouldn't I have to join 2 fact tables in my query to produce the history of a case? I guess what I am failing to understand is the rule for why and when I would create a new fact table. I thought the fact table represented a business process, and our fact_case table tracks the history of a case from "new", "open", "contact made", "supervisor review", "re-assigned", ending with "completed", "incomplete", or "partial" case. Based on your reply, significant events are re-assignment, mode switch, QA/QC, etc. I would need at least 3 additional fact tables, correct? That would mean 4 joins to get case history.