Surrugated key in fact table

I´m in charge of making the telemarketing database in my company so Ive made a fact table with a atom grain of one record for every telephone for channell for campain and I include a surrugate key and a child key so this way I can make a inner join and have a trace of the calls

the idea is to have a way of tracing exactly the history of outbound calls we have for a client but also if one was because of another the first part you can get from a simple order by date but the second part you need to include the Id of the previous call id

It works like the menu and submenu in some websites

any coments ???

I wonder if it is overkill...

A simpler solution would be to carry a thread ID on related calls rather than a child (or parent) key. This option will not allow you to represent splits in the thread, but it doesn't seem that is needed in this case.

The advantage is all related calls would carry the same thread ID. Getting a list of related calls becomes a much simpler query. Using a parent/child relationship would require a bridge table or a recursive SQL query.

I like your suggestion, It would work like this forum you see all the replays that originated from the first contact... but in some way you still need two columns; one for the id_sk (thread ID) and another to hold the original thread ID in the other contacts

Did I get you right? or can you give me an example of the table design your talking about

You would have two columns: a call ID that is unique for each call and a thread ID that is shared across calls in the same thread.

When a new, unrelated call is initiated, you create a row where the call ID and thread ID are the same value. If another call is made that relates to the previous call, you get the thread ID from the previous call and use it as the thread ID for the new call. Subsequent dependent calls would do the same thing, getting the thread ID from the prior call.