Snowflake issue

I have a Time Slip fact table to record the time usage of our employees. Each Time Slip should be associated with a record in the Project dimension table. Each Project should have a Client.

The problem now is should I have a Client dimension and have a Client_Key in the Time Slip table or should I denormalize the client information into the Project dimension table? In the requirement, I do have other fact tables should be associated with a Client dimension table and should not be associated with a Project dimension table.

Thanks.

Your question sounds like a similar one I had for a system I am working on.

I think if you can keep the Client Dimension "Conformed" since it is used in other fact tables and then denormalize some of the client data stuff into the Project dimension you can remain flat and still meet your needs.

It took me a while to realize the benefits of keeping my model flat as possible and avoiding Outrigger or snowflaking as much as possible.

Add a client key to the time slip fact table.

I suppose I should add a Client_Key/Client_ID in the Project dimension table too. Should I use the Client_Key or Client_ID? Should I enforce the FK constrain? Thanks.

A dimension table should always have a surrogate primary key. Always use the surrogate key as the FK in the fact table. Never use the natural key.

FK contraints are superfluous. They are not needed.

The current situation is both the Client table and the Project table are dimension tables. They have a one-to-many relationship (one client has many projects). Do I need to reflect this relationship in the design?

Thanks.

Depends. If there is already a fact that tracts a project from inception, one would expect client is a dimension of that fact. That should be more than enough to support reporting project/client relationships.

What if I do want to keep this relationship between (client dimension and project dimension), just in case users would like to roll-up from Project dimension to Client dimension, should I use the Client_Key (surrogate key) or Client_ID (natural key) in the Project dimension?

Thanks.

thomaszhwang wrote:What if I do want to keep this relationship between (client dimension and project dimension), just in case users would like to roll-up from Project dimension to Client dimension, should I use the Client_Key (surrogate key) or Client_ID (natural key) in the Project dimension?

Thanks.

I would put the Client_Key (surrogate key) in the Project dimension, even more, I would make it SCD2. The only thing to bother is not to allow too many rows to be propagated due to a changed parent dimension's SCD2 attributes.