Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    24

    Asylum seeker procedure database- any suggest?

    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.

    REQUIREMENTS:
    - 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).

    Click image for larger version. 

Name:	CTRPI.png 
Views:	5 
Size:	145.3 KB 
ID:	16904

  2. #2
    Join Date
    Feb 2007
    Posts
    24
    I slightly changed the design to include a reference between Request and PersonalData.
    Do you have any design suggest to keep track of all person aliases ?
    Click image for larger version. 

Name:	CTRPI_en.png 
Views:	1 
Size:	42.5 KB 
ID:	16912

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,832
    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.

  4. #4
    Join Date
    May 2016
    Posts
    81
    Hi Liuk

    Very interesting project.

    About data modelling rules, if there are 1 to 1 cardinalities between entities in the both ways, the entities have to be merged.

    Could you precise me

    1. what happens if a convocation is
      • cancelled
        • => New convocation or request cancelled too

      • postponed
        • =>new convocation?
        • => new commission with new members?

    2. If you need to store the historic of convocations or do you overwrite previous schedule information with new ones


    Pending the receipt of responses to the question posed, I propose you spontaneously this conceptual data model draft

    Click image for larger version. 

Name:	asylum_cdm.jpg 
Views:	8 
Size:	115.0 KB 
ID:	17052

    Hypothesis are:
    1. the scheduled dates for convocations are not stored. It means the postponed date is overwritten with the last current convocation date. I keep First Convocation date for information.
    2. the stored status is the current active status. You don't need to store the historic of status
    3. The stored commission members are the current available/present members. You don't need to store the historic of commission members for a request
    4. The stored interpreter is the current available/present interpreter. You don't need to store the historic of interpreters for a request
    Last edited by informer; 07-16-16 at 08:46.

  5. #5
    Join Date
    May 2016
    Posts
    81
    Hi Liuk
    Erratum
    cardinalities between seeker and asylumRequest are 1 to n and 1 to 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •