Need help to design table which is can store Education Requirement for the Job Vacancy
-
Hi,
This is my table named - CV
*CVID is a primary key
This is my table named - CVEducation
*CV to CVEducation is 1 to Many. CVEducation(CVID) is a foreign key
This is my table named - Level
*LevelID is stored in CVEducation. So, we can know - This education is Diploma or Degree or Masters or so on
This is my table named - StudyField
*StudyFieldID is stored in CVEducation. Som we can know - What kind of Education they have.
This is the example of Job Vacancy 1
1- Need Diploma in Chemistry OR Degree in Chemistry.
2- CGPA at least - 2.5
*May be need to store 2 rows with each CGPA
This is the example of Job Vacancy 2
1- Need Diploma in Commerce OR Diploma in Economics OR Degree in Biology
2- CGPA at least - 2.8
*May be need to store 3 rows with each CGPA
How to design my table to store Job Vacancy Minimal Requirement? I know, there's some cases we cannot computerized. But, at least some of Job Vacancy Requirement we can computerized.
Really need help
-
Not sure what the question is here. Where does job vacancy come from? The structure you have posted seems reasonable enough but I don't at all understand where all of a sudden you want something job vacancy. Help us understand the problem and we can help you with solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (8/13/2014)
Not sure what the question is here. Where does job vacancy come from? The structure you have posted seems reasonable enough but I don't at all understand where all of a sudden you want something job vacancy. Help us understand the problem and we can help you with solution.Hi Sir,
This is the example of Job Vacancy 1
1- Need Diploma in Chemistry OR Degree in Chemistry.
2- CGPA at least - 2.5
*May be need to store 2 rows with each CGPA
This is the example of Job Vacancy 2
1- Need Diploma in Commerce OR Diploma in Economics OR Degree in Biology
2- CGPA at least - 2.8
*May be need to store 3 rows with each CGPA
How can I store the Job Minimal Requirement? How to design the table to store some kind of expression.
-
Qira (8/13/2014)
Sean Lange (8/13/2014)
Not sure what the question is here. Where does job vacancy come from? The structure you have posted seems reasonable enough but I don't at all understand where all of a sudden you want something job vacancy. Help us understand the problem and we can help you with solution.Hi Sir,
This is the example of Job Vacancy 1
1- Need Diploma in Chemistry OR Degree in Chemistry.
2- CGPA at least - 2.5
*May be need to store 2 rows with each CGPA
This is the example of Job Vacancy 2
1- Need Diploma in Commerce OR Diploma in Economics OR Degree in Biology
2- CGPA at least - 2.8
*May be need to store 3 rows with each CGPA
How can I store the Job Minimal Requirement? How to design the table to store some kind of expression.
Your requirement is extremely unclear but I will take a shot in the dark.
I think you are wanting add JobVacancy to the system? I would do this with a job Vacancy table. That would hold the ID of the job, start dates, salary ranges that sort of stuff and possibly the minimum CGPA requirement. Then for the details you would need a second table. Something like JobVacancyRequirements. It would have a foreign key the JobVacancy row and have a column for LevelID (foreign key to Level) and a column for StudyFieldID (foreign key to StudyField). That make sense? Is it what you are trying to do?
You absolutely do NOT want to store 3 rows each with the CGPA. This seems like a standard master/detail type of structure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
As to the current design, I'd make these comments:
CVEducation
InstituteName should 100% be normalized out to another table.
DegreeName should 98% be normalized out to another table.
Maybe SponsorID should be in a separate table because there can be multiple?
As to the new requirement, I'd suggest 4 tables (at least -- when you normalize, tables multiply like rabbits):
/*
JobVacancy::
( JobVacancyId, JobId, EarliestDateToApply, EarliestStartDate, ... )
1 = <your sample job vacancy #1>
2 = <your sample job vacancy #2>
JobVacancyRequirementCategory::
( CategoryId, Description, ... )
100 CumulativeGPA
200 EducationDegreeLevel
JobVacancyRequirement::
( JobVacancyId, CategoryId, MinimumPoints )
1, 100, 1
1, 200, 1
2, 100, 1
2, 200, 1
JobVacancyRequirementDetail:: JobVacancyId, CategoryId, Field, Level, Points
1, 100, 0, 2.5, 1
1, 200, 9, 1 , 1
1, 200, 9, 2 , 1
2, 100, 0, 2.8, 1
2, 200, 6, 1 , 1
2, 200,10, 2 , 1
2, 200,13, 2 , 1
*/
You compare the person's credentials to the JobVacancyRequirementDetail, summing their points in each category. If their points don't meet the minimum total, they don't qualify. This scheme allows you to add specific requirements easily just by manipulating the point values.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I consider input from Mr Sean Lange and Mr ScottPletcher. So, here it is..
1- This is my Job Notice / Job Vacancy table
*JobNoticeID is a primary key
2- This is Job Notice Requirement 1 table
*JobNotice to JobNotice_DetailRequirement_1 is 1 to 1. JobNotice_DetailRequirement_1(JobNoticeID) is a foreign key
3- This is Job Notice Requirement 2 table
*JobNotice to JobNotice_DetailRequirement_2 is 1 to MANY. JobNotice_DetailRequirement_2(JobNoticeID) is a foreign key
/*This is existing design and several data*/
4- This is my table named - CVEducation
*CV to CVEducation is 1 to Many. CVEducation(CVID) is a foreign key
5- This is my table named - Level
*LevelID is stored in CVEducation. So, we can know - This education is Diploma or Degree or Masters or so on
6- This is my table named - StudyField
*StudyFieldID is stored in CVEducation. Som we can know - What kind of Education they have.
PLEASE CONSIDER - Job Notice Requirement 2 table
*JobNotice to JobNotice_DetailRequirement_2 is 1 to MANY. JobNotice_DetailRequirement_2(JobNoticeID) is a foreign key
Consider 2 record. It's -2147483648 and -2147483647.
How to make it the Job Requirement is OR
1- -2147483648 OR -2147483647
This mean,
Applicant have LevelID=12, StudyFieldID=8 and NO CGPA
OR
Applicant have LevelID=2, StudyFieldID=10 and CGPA between 2.00 - 4.00
is QUALIFY for JobNoticeID=6
SOME CASES MAY BE
Applicant have LevelID=12, StudyFieldID=8 and NO CGPA
AND
Applicant have LevelID=2, StudyFieldID=10 and CGPA between 2.00 - 4.00
is QUALIFY for JobNoticeID=6
THE INPUT SCREEN MAY BE AS FOLLOWING,
Really need help :crying:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply