Client Name Mapping Table Shape

[Johnsnowlife]

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. 

OurUniqueID	ClientName
1	Sherlock Holmes
2	John 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:

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

Short, broad table:

ClientMapKey	ClientName	OurUniqueID	Channel1ID	Channel2ID	Channel3ID
1	Sherlock Holmes	1	123	800175	null
2	John Watson	2	987	null	ABC456

[v-sihou-msft · Accepted solution]

@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,