Whether sales (fact) table should be made to have the userid from customer (dim) table in dimensional modeling?

Question

I have following relationships:

User (userid, username)

Customer (customerid, userid, customername)

Sales (salesid, customerid, saledate, saleamt)

In the DW I am loading the data as follows:

User (userid, username)

Customer (customerid, userid, customername)

Sales (salesid, customerid, saledate, saleamt, userid)

Note: user id is actually not on the fact table but I am adding it by looking it up from the respective customer record, in order to make the model a star schema: User filters Sales; customer filters Sales.

Is it better to not do this and have the model as a snow flake such that user filters customer; and customer filters sales. (that is - not have the userid in the sales fact table)?

Answer 1

The standard practice is not to create a Snowflake design - so all your Dims will be linked directly to the Fact and you don’t have FKs on Dims referencing other Dims.

So unless you have a very good reason not to do so, this is the pattern you should be using.