Relationship Problems (regarding tables and database design)

[lugnutmonkey]

I have a pretty nasty design problem. I am working on adatabase that tracks phone calls. Currently the calls table is related to thedatabase by the extension dialed. So that each phone call has a unique id thatis linked to a representative through her extension and each representative isa member of a department.



The problem iswhen a Rep. moves between departments he/she may or may not take theextension with them. Additionally that extension may or may not bereusedwithin the same or another department. As you can see this creates apretty tricky problem because I cannot count on a representative having aunique extension that follows them and no extension is unique to anydepartment, in the long run.

I understand that this is a bit of head banger(at least it is for me) but I amopen to any and all suggestions.

FYI: The way this problem is currently being handled is that the extension isassociated with a Rep. as a field in the representatives table. When the Rep.is given a new extension we create a new record for that representative withthe new extension along with an active_to/active_from field so that theextension can be searched properly according to date when looking back into thearchives. I didn’t come up with this method, it was inherited, and I think itopens up the possibility for too much error going forward.

[pbaldy]

It sounds like you want to store a representative ID rather than the extension.