How to Practically Use a Primary key and Foreign Keys for Bank Data

[Karen1015]

I have a question. I want to know how to use a primary key and foreign keys practically. 

A Bank has different departments, such as for credit cards, saving accounts, and for loans. So, it has different customer lists for credit cards, saving accounts, and loans. But the customer ID may exist in more than in a customer list. For example, if a customer uses a credit card and a saving account in the same bank, the customer exits in the credit card table and the saving account table.

How to make the customers from different departments relative to one another?

The only way as below I come up with is combining all customer lists from different departments to get a unique customer list, and then use it as a primary key table to build connections with one another.

credit card table

Customer ID	Types
A123456	....
A123789	...

saving account table

Customer ID	Amount
A123456	...
A224455	...

Loan account table

Customer ID	Amount
A123147	...
A123789	...

To get a primary table, merge the credit card table, the saving account table, and the loan account table.

Customer ID

A123456

A123789

A224455

A123147

Is any other solution to solve it? Can it solve by using Dax? Because there will be millions of data from different departments. Merging all of them to get a unique customer ID costs a lot of time. Thank you.

[johnt75 · Accepted solution]

That is the correct approach to use, to have one table containing the unique customers which then has relationships to all the other tables.

There are different ways of achieving the unique list. There may be a table you can get from the source which already contains this information. If not then Power Query is probably the best place to do it. You can take a reference to each of your existing tables, delete all the columns except customer key, remove duplicate values, append all the queries together and then remove all duplicate values again. It would probably be worth doing some testing to see if removing the duplicates before doing the append improves performance or not.

As a last resort you could do it in DAX by creating a table like 

Unique Customers =
DISTINCT (
    UNION (
        DISTINCT ( 'Bank account'[Customer ID] ),
        DISTINCT ( 'Credit Card'[Customer ID] ),
        DISTINCT ( 'Loan'[Customer ID] )
    )
)