I have a system called document management system. it is used to organize all the documents in the company.
the primary key for each document should be composed from 3 fields.
which are department_id , file_id and document_id.
all the files_id are sorted accordding to its related department.
ex. for document reference no
document reference no.: 9/13/95
this means that this document is related to:
the problem is:
when I want to add another document in another department and another file
an auto no. will be generated ex. 96. and the document reference no. will be ex. 7/45/96
another document will 97.
I need a solution to have this auto no. to be like this:
once I add a doucment to a new file in a specific department it should be started from 1 for each file
and so on
see that file 9/8(department_id:9 and file_id=8) is different from 7/8 (department_id:7 and file_id=8)and each one holds defferent document starting from one.
please, I need to automat the document reference no.