NULL key in fact table or NULL row in dimension?? 1

[etistudent]

Hi!

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 ETL project performance or maybe better for future queries to warehouse??

 

[johnherman]

In a star schema design, it is preferable to create a specific surrogate key (such as -1) for nulls. Then create a "null" customer in the customer dimension with key value -1 and a "null" vendor with key value -1 (or -2 or whatever) in the vendor dimension. I like to use negative key values for purposes such as this to easily distinguish it from other keys.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright