Modeling advice for hiring application scenario

satishjayan Wed Dec 19, 2012 3:06 am

I need suggestions in modeling the below scenario based on the business rules mentioned below. Our Online application is primarily focused towards candidate sourcing and screening process.

Below is the hiring manager view, where he specifies how many positions he/she is looking to open. As soon as he puts a value greater than zero in either of the positions mentioned below, a new requisition ID should be created with a date on which the requisition is created. For e.g.; the hiring manager puts in the value of 2 under service Clerks, then the requisition ID of 101 and 102 are created and assigned to each of the applicants who are applying for the Service clerk position.

Business Rules -

If one of the two positions is closed, then the value in the Service Clerk (positions) should decrement to 1.

If the hiring manager initially consider to open two full time positions and later he changed his mind to fill 4 part time candidates instead of 2, then 2 new requisition id’s should be created e.g.; 103, 104 and these should get assigned to all the applicants who are applying for the Service clerk position.

By mistake if the hiring manager updated the no of Service clerk positions to 6 instead of 4 part time and save the data. Then there should be some means to obstruct the requisition ID creation automatically. How should we restrict this scenario?

Below are the two tables that I started with –

Applicant – User applying for open positions.
Requisition – A new requisition is created each time the Hiring manager creates an open position.

Hiring Manager view - For store 2345, Hiring manager created 2 openings for Service clerk, 1 opening for photo and 3 in Cosmetics

Store – 2345
Current Openings
Service Clerks 2 Photo 1 Cosmetics 3

Applicant –
Application ID Position Name Requisition ID
1 Service Clerk 101, 102
2 Service Clerk 101,102
3 Service Clerk 101,102

Requisition -
Requisition ID Requisition
Date End Date
101 12/18/2012 12/20/2012
102 12/18/2012 null

BoxesAndLines Wed Dec 19, 2012 12:27 pm

While I would enjoy modeling your scenario, your question is posted in the wrong forum. You need an application database, not a reporting database. In no case should you build a dimensional model.

satishjayan Wed Dec 19, 2012 12:53 pm

I understand that. However, I don't see any good forum to address application modeling suggestions. Thought I should try here. Please assist me in providing your advice.

BoxesAndLines Wed Dec 19, 2012 3:38 pm

Well, it is a slow day. :-)

You need a position table. Then a Position Requisition table. Applicants apply for positions, not requisitions, so you need an Applicant Position table as well. See if that helps your thought process.

Sponsored content

Modeling advice for hiring application scenario

Modeling advice for hiring application scenario

Re: Modeling advice for hiring application scenario

Application DB Modeling

Re: Modeling advice for hiring application scenario

Re: Modeling advice for hiring application scenario