Deleting Dimensions and Bridge Dimensions

[remenaker]

I am trying to determine the best way to handle deleted dimensions and relationships in my data warehouse design. My example is the role a user belongs to in our organization. I have both a DIM_USER and DIM_ROLE table that are both treated as Type 1 SCD. We are an education institution so an example of a role would be Student, Staff, Faculty. Each of these tables is fed from there own individual CDC (change data capture) table that records all Inserts, Updates and Deletes on the respective source table. In between those is a DIM_USER_ROLE_BRIDGE table that I intended to treat as a Type 2 SCD. It is fed from it's own CDC table for the relationship between the two. So the question is what do I do when someone is a Staff and a Student and then the cease to be a Student, but are still Staff? I have valid date ranges on my dimensions and bridge tables, so do I just update the last date, or should I add fields to say it's deleted, or is this just a bad practice? Here are what my dimension tables look like:

CREATE TABLE DIM_USER
(
DIM_USER_ID INTEGER NOT NULL
, AUTH_USER_ID INTEGER NOT NULL -- Source system key.
, USERNAME VARCHAR2(255) NOT NULL
, FIRST_NAME VARCHAR2(255) NOT NULL
, LAST_NAME VARCHAR2(255) NOT NULL
, EMAIL VARCHAR2(255) NOT NULL
, VERSION INTEGER NOT NULL
, VALID_FROM TIMESTAMP NOT NULL
, VALID_TO TIMESTAMP NOT NULL
, LAST_UPDATE TIMESTAMP NOT NULL
)

CREATE TABLE DIM_ROLE
(
DIM_ROLE_ID INTEGER NOT NULL
, IVY_META_ROLE_ID INTEGER NOT NULL -- Source system key.
, NAME VARCHAR2(255) NOT NULL
, VERSION INTEGER NOT NULL
, VALID_FROM TIMESTAMP NOT NULL
, VALID_TO TIMESTAMP NOT NULL
, LAST_UPDATE TIMESTAMP NOT NULL
)

CREATE TABLE DIM_USER_ROLE_BRIDGE
(
DIM_USER_ID INTEGER NOT NULL
, DIM_ROLE_ID INTEGER NOT NULL
, VERSION INTEGER NOT NULL
, VALID_FROM TIMESTAMP NOT NULL
, VALID_TO TIMESTAMP NOT NULL
, LAST_UPDATE TIMESTAMP NOT NULL
)

Thanks...

[ngalemmo]

Generally, just the dates should be fine. It is more common to have a current flag than a delete flag, since the current flag allows users to query the current state without having to look for rows where now is between the effective & expiration dates. But it is more a convenience than a necessity. The delete flag, if it is only set on the last row of a series of versions, does give a bit more information than just the dates alone... it essentially indicates there are no future versions. It may be useful.