Modelling customer and a fact

Question

Hello All,

We have a customer table and Loans fact table

customer table

custid custname country

Loans

custID ParentID GroupID loanamount.

Currently we have created 3 alias table for customer and joined with loans on the keys in loan table

customer.ID = Loans.custID

groupCustomer.ID=Loans.ParentID

ParentCustomer.ID=Loans.GroupID

Do we have a better approach than this? Can we manage it with hierarchy and a single customer table?

Thanking you in advance for any points.

Thomas Dodds · Answer

+1 to @Gianni Ceresa

in essence you have the option (or both):

Alias the dimension ...

customer.ID = Loans.custID

groupCustomer.ID=Loans.ParentID

ParentCustomer.ID=Loans.GroupID

Alias the fact ...

CustomerLoans.custID = Customer.ID

ParentLoans.ParentID = Customer.ID

GroupLoans.GroupID = Customer.ID

- with aliased dimension you can choose discreet loanID and see all the parties involved with it (Customer, Parent & Group)

- with aliased fact you can chose a discreet customerID and see all the loans that they are involved with (Customer, Parent & Group)

Gianni Ceresa · Answer

Hi,

Your customers doesn't have any hierarchy structure, it's a flat list with just an ID, a name and a country.

And I guess a customer acting as "parent" for a loan can also be the customer acting as "group" or "customer" for another load.

That's why you probably don't have an hierarchy in your customer table, because it isn't really one.

You can't really have a single customer object but with 3 different joins, except if instead of 3 aliases for customers you create 3 aliases for loans and your model is a kind of 3 fact tables with 1 dimension.

It all depends on what kind of analysis you want to do, but in a way or another the join between 2 objects is unique ....