Each of the database tables corresponds either to an entity or to a relationship in the Entity-Relationship Diagram.
If the entity name is Xxxxx then the corresponding table name
is usually catXxxxx
catAdvert |
recruitment advertisement information |
catApplicant |
the main table -- holding information about applicants |
catApplicantCaps |
suitable jobs for the applicant |
catApplicationStatus |
the allowed values for the status of an application |
catAttachment |
the mail attachments for an application -- including the CV itself |
catBlocked |
mail addresses and mail domains may be blocked so that CVs are not accepted from such addresses/domains |
catContact |
details about contacts -- agencies, advertisers, referees etc. |
catContactType |
the types of contact |
catCvHandler |
handlers for different job types at a given stage in the recruitment process |
catCvSource |
the source of a CV |
catInterview |
details about a particular interview |
catInterviewStatus |
allowed status values for interview |
catInterviewType |
allowed type of an interview |
catInterviewer |
an interviewer for a given interview |
catInterviewerCaps |
the job types for which a person can interview |
catJobType |
the type of jobs on offer |
catJobSpec |
descriptions of the jobs on offer |
catNotes |
notes attached to an application during the recruitment process |
catReference |
a reference for an applicant |
catReport |
a customised report |
catReportHeadings |
column headings for a customised report |
catReportParams |
parameters for a customised report |
catRoom |
a room used for interviews |
catTitle |
a title for an applicant |
Recruitment advertisement information.
Information is held about job adverts so that reports can be done to show the effectiveness of a given advert/advertiser in generating applications etc.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| advert | varchar(32) | Y | Y | unique reference for an advert | |
| description | varchar(128) | Y | textual description for the advert | ||
| advertiser | varchar(32) | catContact(objId) | Y | the advertiser with which this advert is placed | |
| startDate | timestamp | Y | date the advert becomes available | ||
| endDate | timestamp | Y | final date the advert is available | ||
| cost | integer | cost of placing the advert |
The main table -- holding information about applicants.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | Y | Y | unique identifier for an applicant | |
| creationTime | timestamp | Y | date & time when this application was received | ||
| summary | varchar(128) | Y | a summary of the applicant -- this is used to hold the first name and surname as a single string | ||
| soundex | varchar(32) | Y | token representing the 'sound' of the summary -- used for identifying duplicate applications | ||
| source | varchar(32) | catCvSource(source) | Y | the type of source generating the application -- e.g. a recruitment agency | |
| givenName | varchar(64) | the given or first name of the applicant | |||
| surame | varchar(64) | Y | the surname or family name of the applicant | ||
| initials | varchar(8) | the applicant's initials | |||
| title | varchar(32) | catTitle(title) | the applicant's title | ||
| varchar(128) | primary email address for the applicant | ||||
| secondaryEmail | varchar(128) | secondary email address for the applicant | |||
| homePhone | varchar(64) | home telephone number | |||
| workPhone | varchar(64) | work/daytime telephone number for the applicant | |||
| mobilePhone | varchar(64) | mobile telephone number for the applicant | |||
| postalAddress | varchar(254) | applicant's postal address | |||
| applicationStatus | varchar(32) | catApplicationStatus(applicationStatus) | Y | the status of this application | |
| handler | varchar(64) | the user id of the handler who is dealing with this application at the current stage in the recruitment process | |||
| interviewType | varchar(64) | catInterviewType(interviewType) | the type of interview required/performed for this applicant | ||
| sponsor | varchar(64) | name of the person recommending this applicant -- used if the 'source' column holds 'recommendation' | |||
| agency | varchar(32) | catContact(objId) | id of the agency which generated this application -- used if the 'source' column holds 'agency' | ||
| advert | varchar(32) | catAdvert(advert) | id of the advert which generated this application -- used if the 'source' column holds 'advert' | ||
| bestFitJob | varchar(32) | catJobType(jobType) | the type of job most suitable for this applicant | ||
| offerDate | timestamp | date when this applicant was offered a job | |||
| rejectDate | timestamp | date when this applicant was rejected | |||
| holdDate | timestamp | date when this applicant was put on hold | |||
| rejectStage | varchar(64) | stage in the recruitment process at which this applicant was rejected | |||
| acceptDate | timestamp | date when this applicant was offered a job | |||
| joinDate | timestamp | date when this applicant is to join the company | |||
| declineDate | timestamp | date when this applicant declined their job offer |
Suitable jobs for the applicant.
Each applicant has one or more jobs for which they are considered suitable.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | catApplicant(objId) | Y | the id of the applicant | |
| jobType | varchar(32) | catJobType(jobType) | Y | the suitable job type |
The allowed values for the status of an application.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| applicationStatus | varchar(32) | Y | Y | the status value | |
| seqno | integer | Y | Y | the sequence in which status values are presented to the user when listed -- this is the typical order of statuses for an application through the recruitment process |
The mail attachments for an application -- including the CV itself.
The unique identifier for the attachment is the pair (objId, seqno).
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | catApplicant(objId) | Y | id of the application having this attachment | |
| seqno | seqno | Y | sequence number of the attachment in the received application | ||
| attachmentName | varchar(32) | Y | short descriptive name for the attachment -- e.g. CV for the
attachment representing the applicant's CV
|
||
| mimeType | varchar(128) | Y | the MIME type identifying the type of data stored | ||
| data | longvarchar | Y | the base64-encoded data representing the attachment |
Mail addresses and mail domains may be blocked so that CVs are not accepted from such addresses/domains.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| domain | varchar(128) | Y | Y | mail address or domain to be blocked | |
| reason | varchar(128) | Y | reason for the blockage | ||
| dateBlocked | timestamp | Y | date the block was applied | ||
| blockedBy | varchar(64) | Y | name of the person applying the block |
Details about contacts -- agencies, advertisers, referees etc..
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | Y | Y | unique id for the contact | |
| contactType | varchar(32) | catContactType(contactType) | Y | the type of this contact e.g. agency, referee | |
| description | varchar(128) | brief description of the contact | |||
| contactName | varchar(64) | name of the contact | |||
| contactPhone | varchar(64) | telephone number for the contact | |||
| contactFax | varchar(3264 | fax number for the contact | |||
| contactEmail | varchar(128) | email address for the contact | |||
| domain | varchar(128) | mail domain for the contact |
The types of contact -- e.g. agency, referee, advertiser.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| contactType | varchar(32) | Y | Y | the type of contact |
Handlers for different job types at a given stage in the recruitment process.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| applicationStatus | varchar(32) | catApplicationStatus(applicationStatus) | Y | the status of an application where this handler applies | |
| jobType | varchar(32) | catJobType(jobType) | Y | the type of job which this handler deals with | |
| userId | varchar(32) | cauthSecurity(userId) | Y | the Carapace user id of the handler |
The source where the CV originated.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| source | varchar(32) | Y | Y | unique source name e.g. 'advert', 'agency' | |
| seqno | integer | Y | Y | sequence the sources are presented to the user when listed |
Details about a particular interview.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | Y | Y | unique id for the interview | |
| applicantObjId | varchar(32) | catApplicant(objId) | Y | identifies the applicant to be interviewed | |
| room | varchar(32) | catRoom(room) | Y | the interview room | |
| startTime | timestamp | Y | date & time the interview is due to start | ||
| duration | integer | Y | expected interview duration (minutes) | ||
| interviewType | varchar(64) | catInterviewType(interviewType) | Y | the type of interview being performed | |
| interviewStatus | varchar(32) | catInterviewStatus(interviewStatus) | Y | the status of this interview -- e.g. 'arranged', 'cancelled' | |
| recommendation | varchar(32) | Y | the recommendation made as a result of the interview | ||
| outcome | varchar(32) | catOutcome(outcome) | the outcome of the interview -- e.g. 'proceed', 'reject' | ||
| arrangedBy | varchar(64) | cauthSecurity(userId) | user id of the handler Carapace user who arranged this interview |
Allowed status values for interview.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| interviewStatus | varchar(32) | Y | Y | unique status name |
Allowed type of an interview.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| interviewType | varchar(64) | Y | Y | unique name of the type of interview -- e.g. 'telephone interview' | |
| seqno | integer | Y | Y | sequence number defining the order interview types are presented to the user |
An interviewer for a given interview.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | catInterview(objId) | Y | the id of the interview being performed | |
| interviewer | varchar(32) | cauthSecurity(userId) | Y | the Carapace user id of the person performing the interview |
The job types for which a person can interview.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| interviewer | varchar(32) | cauthSecurity(userId) | Y | the Carapace user id of the person who can perform interviews | |
| jobType | varchar(32) | catJobType(jobType) | Y | Y | the job type for which the person can interview |
Descriptions of the jobs on offer.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| jobType | varchar(32) | catJobType(jobType) | Y | Y | unique id for the job on offer |
| mimeType | varchar(128) | Y | the MIME type identifying the type of data stored | ||
| data | longvarchar | Y | the base64-encoded data representing the attachment |
The type of jobs on offer.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| jobType | varchar(32) | Y | Y | unique id for the job on offer |
Notes attached to an application during the recruitment process.
Whenever a significant action is done to an application, the person performing the action is given the opportunity to attach some notes. For example, if an application is rejected, the handler is prompted to attach a reason for rejection.
General notes can be attached to an applicant's record -- e.g. medical records, test scores etc.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | Y | Y | unique identifier for this note | |
| userId | varchar(64) | cauthSecurity(userId) | Y | Carapace user id of the person attaching the note | |
| applicantId | varchar(32) | catApplicant(objId) | Y | the application which has this note attached | |
| interviewId | varchar(32) | catInterview(objId) | identifier of an interview -- used to attach a note to an interview as well as an application | ||
| creationTime | timestamp | Y | date & time the note was attached | ||
| noteType | varchar(32) | Y | description of the type of this note -- e.g. 'reject reason' | ||
| mimeType | varchar(32) | Y | identifies the type of data held in the notes field |
||
| notes | longvarchar | Y | the base64-encoded data for the note |
A reference for an applicant.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| objId | varchar(32) | Y | Y | unique identifier for this reference | |
| appId | varchar(32) | catApplicant(objId) | Y | identifies the application to which this reference applies | |
| referee | varchar(32) | catContact(objId) | Y | identifies the detail for the referee who produced this reference | |
| dateRequested | timestamp | date & time the reference was requested from the referee | |||
| dateReceived | timestamp | date & time the reference was received from the referee | |||
| mimeType | varchar(32) | Y | identifies the type of data held in the notes field |
||
| notes | longvarchar | Y | the base64-encoded data for the reference |
A customised report.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| queryName | varchar(64) | Y | Y | unique name for the report | |
| description | varchar(128) | Y | brief description of the report | ||
| query | longvarchar | Y | the SQL query for the report |
Column headings for a customised report.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| queryName | varchar(64) | catReport(queryName) | Y | the query to which this heading applies | |
| heading | varchar(64) | Y | text for the column heading | ||
| seqno | integer | Y | sequence number of the heading |
Parameters for a customised report.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| queryName | varchar(64) | catReport(queryName) | Y | the query to which this parameter belongs | |
| paramName | varchar(32) | Y | parameter name | ||
| seqno | integer | Y | sequence number of the parameter | ||
| paramType | varchar(32) | Y | data type for the parameter | ||
| defaultExpr | varchar(128) | Y | expression to evaluate which yields the default value for the parameter | ||
| enumSql | varchar(254) | Y | the SQL which returns the list of values for enumerated parameters |
A room used for interviews.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| room | varchar(32) | Y | Y | name of the room |
A title for an applicant.
| column | data type | references | mandatory | unique | description |
|---|---|---|---|---|---|
| title | varchar(32) | Y | Y | the title e.g. Mr, Mrs | |
| seqno | integer | Y | Y | sequence number used for ordering the titles when displaying to the user |
| Contents | Index | Current topic: Carapace Application Tracker | Related topics: CAT Contents |