1

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.

  • 1
    @ElGrig You might find of help several deliberations included in this Q & A. – MDCCL Apr 29 at 22:26
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.