What kind of approach is this in SQL, it actually exists? Is it viable/good pratice?

Question

One of our teachers gave us the following challenge:

"Make a Database schema with the following principle: you can't change any values on any table, only add new ones."

I came with the following schema:

    CREATE TABLE TRANSACTIONS(ID PRIMARY KEY, TRANSACTION_TYPE_FK, DATE);

    CREATE TABLE TRANSACTION_TYPE(ID PRIMARY KEY, NAME);

    CREATE TABLE PRODUCTS_TRANSACTIONS(ID_PROD_FK, ID_TRANS_FK, MONEY,  QTY);

    CREATE TABLE PRODUCTS(ID PRIMARY KEY, NAME, PRICE_FK );

    CREATE TABLE PRICES(ID PRIMARY KEY, DATE, DETAILS);

It's just a proof of concept. Basically everything is based on transactions.

Transactions can be Entry, Exit and Move Products and In & Out Money.

I can control my quantities and cash based on transactions.

The PRODUCTS_TRANSACTIONS "MONEY" field is used if a transaction involves money only or there are "discounts" or "taxes" on the transaction.

The Products Table has a "child" table called "prices", it storages all the price changes , the "details" field is for annotations like "Cost Price" etc.

I made it very quick, I am sorry for any inconsistency.

I liked this kind of approach, I am kinda of a newbie with SQL so I really wanted to know if this approach has a name and if it is viable perfomance-wise or a good pratice.

My idea is making a View and "update" it whenever a new transaction is made, since nothing needs to be "updated" I only need to add new rows to the View.

I am currently very sick, so I can't go to college to remedy my doubts.

Thanks in advance for any help

Don't know what do you mean by "can't use update command to change values" but if you want to maintain history, you can create a trigger on table and store the old/updated data in another table. — Harshil Doshi, Sep 07 '16 at 08:04
The challange is " you cannot change any values on any table, only add it, deleting and remaking it count as change" — LouizFC, Sep 07 '16 at 08:19
Which DBMS (vendor and version)? You might read about `INSTEAD OF TRIGGERs`. They allow you to put your action **before** the DBMS has done anything. You might set a `ValidTo` date of the current line and insert the new values *instead of an update* (or push old values to a history table) — Shnugo, Sep 07 '16 at 08:20

score 1 · Accepted Answer · answered Sep 07 '16 at 10:49

Let's take only one table TRANSACTION_TYPE(ID PRIMARY KEY, NAME) for example:

Now if you want to restrict update on the table, you can achieve that with following queries:

GRANT SELECT,INSERT,DELETE ON TRANSACTION_TYPE TO Username;
                         OR
Deny UPDATE ON TRANSACTION_TYPE TO Username;

Now to maintain history of insertion and deletion,you can store in another table by creating trigger on TRANSACTION_TYPE as follows:

CREATE or REPLACE TRIGGER my_trigger // name of trigger
AFTER INSERT OR DELETE
ON TRANSACTION_TYPE
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO TRANSACTION_INSERT_HISTORY(ID,NAME) //table that maintain history of insertion 
VALUES(:new.ID,:new.NAME);    
ELSIF DELETING THEN
INSERT INTO TRANSACTION_DELETE_HISTORY(ID,NAME) //table that maintain history of deleted records 
VALUES(:old.ID,:old.NAME);
END IF;
END;
 /

Before creating this trigger, you first have to create two tables: TRANSACTION_INSERT_HISTORY(ID,NAME) and TRANSACTION_DELETE_HISTORY(ID,NAME)

I have created two different tables for insertion and deletion for simplicity. You can do it with one table too.

Hope it helps.

score 0 · Answer 2 · answered Sep 07 '16 at 08:09

The table that holds the information, you could give permissions only to insert and select to the table, preventing update.

https://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/

GRANT INSERT, SELECT ON TableX TO UserY

In a production system, you'd probably design this using a VIEW for selecting the data from the table (to only get the most recent revision of the audit data). With perhaps another VIEW that would allow you to see all the audit history. You'd probably also make use of a Stored Procedure for inserting the data and ensuring the data was being maintained in the audit history way you suggest.

What kind of approach is this in SQL, it actually exists? Is it viable/good pratice?

2 Answers2