NULL in fact table as FK or ....

[Luk]

Hi!

This is not strictly DS question but maybe someone had similar situation.
Let's say we have two dimensions: customer and vendor, and one fact table with columns: customer_id, vendor_id and value.
In one row in fact table we can have reference to customer dim or vendor dim (never to both of them).

Is it better to put in customer and vendor dim row with NULLs in all columns and put into facts foregin key indicates to it
or
put into fact table NULL or "0" (foregin key indicates to nothing) as vendor or customer foregin key??

Is one of theese solutions better for DS project performance or maybe better for future queries to warehouse??

[Sainath.Srinivasan]

Your question is better answered by the reporting people. If they need to query both vendor and customer separately and frequently, you are better-off having them separate as otherwise all the SQL in reports must take care of this.

[JDionne]

Hi!

This is not strictly DS question but maybe someone had similar situation.
Let's say we have two dimensions: customer and vendor, and one fact table with columns: customer_id, vendor_id and value.
In one row in fact table we can have reference to customer dim or vendor dim (never to both of them).

Is it better to put in customer and vendor dim row with NULLs in all columns and put into facts foregin key indicates to it
or
put into fact table NULL or "0" (foregin key indicates to nothing) as vendor or customer foregin key??

Is one of theese solutions better for DS project performance or maybe better for future queries to warehouse??

Personaly I would have the fact table with two columns an ID column and a fact column. On the Ref table I would have a flag that would indicate Client or Vender. I say this becuase having Nulls in a FK relationship is not the best thing you can do for perforamace. I have seen databases handle Null in very odd ways. But thats just a DBA's thoughts on the mater.