I have a fact table called ProjectTasks.

It’s a left join of project and tasks table. A project can have 0 or more tasks. Thus for projects that don’t have any task, the task columns are nulls (blanks).

It’s similar to Order Header Detail modelling technique, except that a header can have 0 or more detail rows (rather than the usual 1 or more detail rows).

One of the Task column is RoomName. Each task has a valid (non null/blank) value in this RoomName column.

For projects that don’t have a task, I cannot make the nulls (blanks) as some keyword like ‘Not known’ because there isn’t a task yet. So logically it is a genuine null/blank.

In this scenario, I’m trying to think if it would be a good idea to leave the RoomName column in the fact table or create a dimension for it? If I extract the room name into a dimension table called Room, then it will link to Fact table on the Room name, however nulls (blanks) in fact table will not have any dimension link.

The dimension tables in the models I made in the past always had all the possible values that exist in the fact table. Whereas as explained above, here if I extract the Room into a dimension, then the dim table won’t have the links to the nulls (blanks). Is this acceptable for data modelling?