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

Client Name Mapping Table Shape

We have different products sold through different channels with one product per channel. Each channel has their own unique identifier for the end client but they are different across channels. 

 

In my database I need to map the different channels' unique identifiers to our unique identifier for the end client so we can see who is purchasing multiple products through different channels. 

 

So I have one table, ClientInfo, with a list of the end clients and our unique identifier, OurUniqueID

OurUniqueIDClientName
1Sherlock Holmes
2John Watson

 

Now, I need a table to map the different channels' unique identifiers to our unique identifier. Should I do that in a tall, narrow table with a row for each channels' unique ID (see below) or a short, broad table with a column for each channel which will necessarily have many null entries for the channels where the client doesn't have an ID. 

Tall, Narrow table:

 

ClientMapKeyClientNameOurUniqueIDChannelID
1Sherlock Holmes1123
2Sherlock Holmes1800175
3John Watson2987
4John Watson2ABC456

 

Short, broad table:

ClientMapKeyClientNameOurUniqueIDChannel1IDChannel2IDChannel3ID
1Sherlock Holmes1123800175null
2John Watson2987nullABC456

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Client Name Mapping Table Shape

@Johnsnowlife

 

It's better to create a table like your first one. 

 

ClientMapKey ClientName OurUniqueID ChannelID
1 Sherlock Holmes 1 123
2 Sherlock Holmes 1 800175
3 John Watson 2 987
4 John Watson 2 ABC456

 

Since each client may have dynaic number of Channels, it's not a good practice to make one column for each Channel. You can hardly analyze fact data on Channel level. Please refer to: Third normal form

 

Regards,

1 REPLY 1
Moderator v-sihou-msft
Moderator

Re: Client Name Mapping Table Shape

@Johnsnowlife

 

It's better to create a table like your first one. 

 

ClientMapKey ClientName OurUniqueID ChannelID
1 Sherlock Holmes 1 123
2 Sherlock Holmes 1 800175
3 John Watson 2 987
4 John Watson 2 ABC456

 

Since each client may have dynaic number of Channels, it's not a good practice to make one column for each Channel. You can hardly analyze fact data on Channel level. Please refer to: Third normal form

 

Regards,