How does foreign word transliteration work in the context of Cantonese vs Mandarin?
You're right, most foreign words are transliterated differently in Mandarin and in Cantonese. Sometimes there are even different standards in different Mandarin speaking regions. It's an interesting idea to use characters that have similar pronunciations in both dialects to unify the transliteration but it's not what has already happened. 

A few examples of different transliteration in Mandarin and Cantonese:


Beckham: 贝克汉(姆) (Mandarin: Bèikèhàn[mú]), 碧咸 (Cantonese: Bīkhàahm)
Hollywood: 好莱坞 (Mandarin: Hǎoláiwù), 荷里活 (Cantonese: Hòhléihwuht)
Titanic: 泰坦尼克 (Mandarin: Tàitǎnníkè), 铁达尼 (Cantonese: Titdaahtnèih)


The standards used by mainland China to transliterate English names into Mandarin can be found here: 


英语姓名译名手册 (rules)
常见英美姓名音译表 (list)


I was unable to find a good reference for its Cantonese counterpart though.

Related reading: 


Transcription into Chinese characters (wikipedia)
两岸四地外国专名翻译异同趣谈