Update dim ID in fact rows or create new fact row?

I am designing a fact table to track Student Applications which has a grain of application and status, with a new row when the status changes. Some applications have a free text notes field. I have put these a separate dimension (DimNotes). When no notes exists the fact record points to a generic 'No Note' row in the notes dimension. If a note then gets added at any point, should I update all fact rows with this ID, or should I create a new fact row that includes the new ID, or only create a new fact row with the new ID when the status of the application changes? Thank you

Adding a new fact row is a bit overkill, but it really depends on what you are trying to accomplish.

As far as notes go, frankly, a free form text field is the last thing anyone needs on a fact table. What is anyone actually going to do with it?  Enough ranting...

Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.

Scott wrote:I am designing a fact table to track Student Applications which has a grain of application and status, with a new row when the status changes. Some applications have a free text notes field. I have put these a separate dimension (DimNotes). When no notes exists the fact record points to a generic 'No Note' row in the notes dimension. If a note then gets added at any point, should I update all fact rows with this ID, or should I create a new fact row that includes the new ID, or only create a new fact row with the new ID when the status of the application changes? Thank you

Do you need those old notes or you always want to report on new/latest notes?

Vishwas wrote: Do you need those old notes or you always want to report on new/latest notes?

There is only one note per application. This could get updated over time, but I will just overwrite that in the dimension, so will always see the latest note

ngalemmo wrote: Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.

Dbms is SQL Server, I would still prefer to keep this in a separate dimension as a varchar I think, as generally it won't be queried and not all applications have notes.

Sorry for asking a stupid question but just for clarity in my head, when you say using a surrogate fact key, does that mean in the fact table store the ID of the Note dimension as a foreign key or is this a different concept? My Note dimension just has 3 columns - ID, DurableKey, Note

The 'surrogate fact key' approach is you assign a unique surrogate key to each fact row and use that as the PK of the notes dimension as well. This avoids having to update the fact table.

ngalemmo wrote:The 'surrogate fact key' approach is you assign a unique surrogate key to each fact row and use that as the PK of the notes dimension as well.  This avoids having to update the fact table.

I see, I haven't come across that technique as yet, but that's very useful. I don't think that will quite fit the grain of my fact table, as I create a new fact row each time the application changes status so it's not actually 1:1 relationship.

Scott wrote:

Vishwas wrote: Do you need those old notes or you always want to report on new/latest notes?

There is only one note per application. This could get updated over time, but I will just overwrite that in the dimension, so will always see the latest note

ngalemmo wrote: Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.

Dbms is SQL Server, I would still prefer to keep this in a separate dimension as a varchar I think, as generally it won't be queried and not all applications have notes.

Sorry for asking a stupid question but just for clarity in my head, when you say using a surrogate fact key, does that mean in the fact table store the ID of the Note dimension as a foreign key or is this a different concept? My Note dimension just has 3 columns - ID, DurableKey, Note

Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

Vishwas wrote:
Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

My issue is when the application goes from not having any notes in which it points to a generic 'no value' row in the dimension, to then having a note. Based on what you've said, should I create a row in the dimension for every application regardless of whether they have a note or not?

Scott wrote:

Vishwas wrote:
Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

My issue is when the application goes from not having any notes in which it points to a generic 'no value' row in the dimension, to then having a note. Based on what you've said, should I create a row in the dimension for every application regardless of whether they have a note or not?

Yes one row in notes dimension which will say 'no value' if that application is never going to have any notes associated and its surrogate key will be in fact.
But if a application may come to having notes then yes one row for each one would make more sense

It boils down to what you want to update. Do you want to change the FK on the fact or add/update a row in the dimension?

If the former, the natural key for the note dimension should be the note itself. You only store each unique note once. In the latter, you have rows in the dimension when there is a note and the PK for that dimension should be the same PK as the fact. You would not have rows for 'no note'. Use an outer join to access the note dimension and provide a default when null.

ngalemmo wrote:It boils down to what you want to update.  Do you want to change the FK on the fact or add/update a row in the dimension?

If the former, the natural key for the note dimension should be the note itself.  You only store each unique note once.  In the latter, you have rows in the dimension when there is a note and the PK for that dimension should be the same PK as the fact.  You would not have rows for 'no note'.  Use an outer join to access the note dimension and provide a default when null.

ngalemmo if there are no rows for 'no note', this case would make us insert NULL in surrogate key column in fact, right ?

Yes, and do an outer join to the dimension.

ngalemmo wrote:Yes, and do an outer join to the dimension.

ngalemmo, do you think it is right approach to have a surrogate key column in fact table as not null ? We do make combination of surrogate key work as primary key for fact table.

All foreign keys do not define the primary key of a fact table. A note key, particularly if the note could change, does not help identify a row and cannot be part of a primary key, simply because it can change. The true primary key is the minimum number of columns that actually identify a row.

You can also create one dummy row in the Note dim that would represent a "Null" note and use that row ID whenever and every time you have a null note. This way you do not store null key in the fact table and you don not have to do a outer join.

ngalemmo wrote:All foreign keys do not define the primary key of a fact table.  A note key, particularly if the note could change, does not help identify a row and cannot be part of a primary key, simply because it can change.  The true primary key is the minimum number of columns that actually identify a row.

Thank You ngalemmo