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:
-------------------------------------------------
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.