How to avoid losing information via UPDATEs due to database design issues

database-designdatabase-diagrams

Business requirements

I have a database where information about documents and their related packages are kept. Each document can have multiple packages, and each package has its details and product list.

When documents become certified they cannot be further edited, but users are allowed to edit package details before attaching it to another document, but in certified documents old data should be kept.

Considerations

So my issue is that, when users update package details to create a new document, in front-end views package details get updated even in certified documents because I have only one row for package details in my database.

I am evaluating the possibility of keeping multiple rows for each update, so as to prevent losing the data retained before the updates in already-certified documents, but it will cause too much work. I want to know how to effectively design the relevant database in order to avoid this kind of issues.

My database diagram so far

This is my current design diagram.

Best Answer

The simplest way to implement the requirements inside the database would be to create two separate tables. Then, you just don't GRANT UPDATE on the set of tables used to store the "final documents". This assumes that the end-users are logging in with an account that does not own the tables.

create table not_final_documents ( document_id int );
grant insert,update,select,delete on not_final_documents to end_user_role;


create table final_documents ( document_id int );
grant insert,select on final_documents to end_user_role;

This enforces the requirement of "can't modify final documents" even if the users use a different application to work on the document/packages.

You'll want to create database side procedures (T-SQL, PL/SQL, etc) to handle the data management between not_final_documents and final_documents.

Related Question