SCD (Slowly changing dimension) database model example. This one make sense and perform well?

475 Views Asked by At

On a customer loopback.io project I need to track/audit and provide undo/redo capability, the best I found was the SCD method.

I found something here: https://en.wikipedia.org/wiki/Slowly_changing_dimension then I created this model:

SCD Model

-------------------------------------------------
CREATE VIEW vw_trackedinvoice AS
    SELECT 
        ivp.id AS id,
        ivp.code AS code,
        ivp.idCustomer AS idCustomer,
        trk.id AS trkid,
        trk.idSession AS idSession,
        trk.isCurrent AS isCurrent,
        trk.tableName AS tableName,
        trk.tableCode AS tableCode,
        trk.tableId AS tableId,
        trk.dtIni AS dtIni,
        trk.dtEnd AS dtEnd,
        trk.dtDel AS dtDel
    FROM
        (invoiceversionprovider ivp
        JOIN scdtrack trk ON ((ivp.idscd = trk.id)))
    WHERE
        (trk.tableName = 'invoiceversionprovider');
-------------------------------------------------
CREATE VIEW vw_trackedinvoicedetail AS
    SELECT 
        idp.id AS id,
        idp.code AS code,
        idp.codeInvoice AS codeInvoice,
        idp.idProduct AS idProduct,
        idp.qtd AS qtd,
        trk.id AS trkid,
        trk.idSession AS idSession,
        trk.isCurrent AS isCurrent,
        trk.tableName AS tableName,
        trk.tableCode AS tableCode,
        trk.tableId AS tableId,
        trk.dtIni AS dtIni,
        trk.dtEnd AS dtEnd,
        trk.dtDel AS dtDel
    FROM
        (invoicedetailversionprovider idp
        JOIN scdtrack trk ON ((idp.idscd = trk.id)))
    WHERE
        (trk.tableName = 'invoicedetailversionprovider');
-------------------------------------------------
CREATE VIEW vw_currentinvoice AS
    SELECT 
        trki.id AS id,
        trki.code AS code,
        trki.idCustomer AS idCustomer,
        trki.trkid AS trkid
    FROM
        vw_trackedinvoice trki
    WHERE
        ((trki.isCurrent = 1)
            AND ISNULL(trki.dtDel));
-------------------------------------------------
CREATE VIEW vw_currentinvoicedetail AS
    SELECT 
        trkid.id AS id,
        trkid.code AS code,
        trkid.codeInvoice AS codeInvoice,
        trkid.idProduct AS idProduct,
        trkid.qtd AS qtd,
        trkid.trkid AS trkid
    FROM
        vw_trackedinvoicedetail trkid
    WHERE
        ((trkid.isCurrent = 1)
            AND ISNULL(trkid.dtDel));
-------------------------------------------------

There is some better formal pattern to do it ?

I'm really new to SCD, hope someone with more experience can see it.

0

There are 0 best solutions below