cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Best Practice for equating multiple client names to a single client name

My data comes from different sources and in each source the names for my clients are different. So in my fact table name column I could have "Mr John Doe", "Mr J Doe" and "John Doe" but they all represent the same person.

 

Currently, I have a ClientNameMap table where ClientNameAlias is the primary key which looks as follows:

KeyClientNameClientNameAlias
1John DoeMr John Doe
2John DoeMr J Doe
3John DoeJohn Doe

 

Then I have an additional lookup table for ClientInfo about the client. 

KeyClientNameCityCompany
1John DoeLondonBig Company
2Jane SmithMiamiSmall Company

 

Should I relate ClientInfo[ClientName] to ClientNameMap[ClientName] ? 

Client Name Relationships 1.JPG

 

Or should I relate ClientInfo[ClientName] to ClientNameMap[ClientNameAlias]?  

Client Name Relationships 2.JPG

 

In both cases I then relate ClientNameMap[ClientNameAlias] to Sales[ClientNameAlias] 

Client Name Relationships 1-2.JPG

 

Then I merge Sales with ClientNameMap on the ClientNameAlias column and expand ClientName so that if I want to display a table I can display the official ClientName and not all the different aliases. 

 

Does this make sense? How should I be dealing with the different names for a single client? 

1 ACCEPTED SOLUTION

Accepted Solutions
BeemsC Member
Member

Re: Best Practice for equating multiple client names to a single client name

I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.

 

With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.

1 REPLY 1
BeemsC Member
Member

Re: Best Practice for equating multiple client names to a single client name

I think, judging from what you've shown us. You need to create a relationship between the 2 'Clientname'
The reason you can create a relationship with clientnamealias aswell, is because (in ur example) 1 in 3 have a relation.

 

With the way you data is set-up though, you might in some scenarios see 'John Doe' three times, so be careful about that.