How to handle the following situation? (Sorry, I cannot come up with an appropriate title for this...)

[thomaszhwang]

I have a Project dimension table and a Client dimension table. The business rule is 1 Client can have 0+ Projects and 1 Project must belong to only 1 Client. In this case, for the Project dimension, I de-normalized the Client fields into the Project dimension table

Now I have an Invoice fact table. The business rule is the Invoice could be affiliated to a Project (so as to be affiliated to a Client) or just be affiliated to a Client without a Project.

The Invoice fact table looks like this.

FactInvoice
PK/FK?	Field Name	Required?
PK	Invoice_Key	Yes
FK1	Client_Key	Yes
FK2	Project_Key	No
	Invoice_Amount	Yes

Questions:
1. Is there any problem to design this way?
2. Does the Project dimension table in this case require to include all the Client fields (de-normalization)?

Thanks.

[hang]

I would not de-normalize the Client attributes into Project dimension as they belong to a different base dimension called Client. You should only de-normalize attributes into the base dimension when the containing dimension is regarded as an aggregate dimension, in which case you should build your aggregate dimension based on the base dimension in your ETL process so that you don’t end up with two versions of attributes in different places.

Instead, I would snowflake Project dimension by having an FK to the Client dimension. In your fact table, all you need is the Project_Key as the entry point for the Client-Project hierarchy.

[thomaszhwang]

Thank you hang. This is very helpful.

[VHF]

Can you have invoices that don't have a project associated with them? If not, go with what hang suggested.

If so, keep your original fact table design. However, don't use a nullable FK for Project_Key. FKs in a fact table should never be null. Instead, have a special 'not applicable' row in your project dimension table to point to. You can set all the attributes to 'NA' or whatever makes sense for you when there is no project.

[thomaszhwang]

Yes, for some Invoices, a Project is not required.

[Sponsored content]