I have to design a database to manage part of the asylum seeking procedure here in Italy. Procedure description:
The asylum claim is lodged at the police office where applicant’s information are stored into a database, including personal data (name, last name, date of birth, nationality, etc..). The procedure assign each applicant a unique alphanumeric code (CUICode). This part of procedure is not our focus.
An applicant may submit more than one request over time, not necessarly with the same information. For example he could be rejected the first time, and re-submit another request months later providing other name. The only information cannot change is the CUICode because is associated with his fingerprints.
The decision concerning the international protection application is a task carried out by a commission for recognition of international protection (called CTRPI).
My goal is to develop an application to help commission member and commission secretary employee. Let’s go further.
The CTRPI invite the seeker for a personal interview at a given date (appointment or convocation).
The applicant can ask for a postponement of the hearing for health reason or other serious reasons.
The commission may cancel (and postpone) the hearing (and the convocation) for organizational issues.
The interview is conducted usually by only one member of the commission with the help of an interpreter. The applicant can apply to have the interview with all members of the commission.
During the interview the applicant may provide different personal data (different name, or data of birth, nationality, etc.). All these variations must be recorded somehow.
After the hearing the commission will decide about the request (positive or negative) or may postpone (suspend) the decision (it needs more time or further documentation to decide).
The Commission can decide not to call the applicant to the hearing if it has already made a positive decision based on the documents he/she has handed in and the statements he/she has made to the police.
- give commission members a software to interview the applicant. The software should provide a base questionnaire with a pre-fixed sequence of questions (standard for all applicants). The interviewer must be able to add or remove questions in the questionnaire or change questions sequence.
- in addition to base (standard) questions, the interview can add group of questions based on applicant response, its religion, its origin and so on.
- asylum seekers receive a copy of the interview report
- the secretary employee need a software to organize commission session. A session is confirmed when at least three members confirm their presence.
- employee insert applicant data into the system, and set applicant convocation for interview.
- on each session, commission members login the system confirming their presence for that session. They can list the applicant to be heard for that day and start the interview.
- when commission decide, the decision data are stored into the system by the secretary employee.
I ended up with the diagram below. Do you think it’s good enough?
I need some suggest to implement the commission session scheduling. I’m looking for a solution to implement a calendar that takes count of holidays and office's days off (when commission cannot meet).
man this is such a cool project I want to help you out, but I am about to crash for the night. I promise to look at your ERD this weekend. I was abhorred to hear about the mafia scandal involving this operation in the news of late.