0

I am creating job post application. In my first table the columns are This is my master table

ID
Position
jobDescription
minExp
maxExp
LastDate
InterviewDate
Project
HiringManager
interviewer
Primaryskills
SecondorySkills

and second table contains the primary skills PrimarySkill table

ID
PrimarySkills

third Table contains the secondory skills

ID
SecondarySkills

fourth Table contains the Interviewer

ID
Interviewer

the problem is that if a person have multiple skills and multiple interviewer for a job, Then how can insert the value from primary skill table, secondory skill table and interviewer table in Master table. i do not want to store value in comma separated

Improve this question
1
  • 4
    It sounds like you don't understand the relational model at all. Commented Feb 25, 2014 at 11:08

2 Answers 2

Reset to default
2

I don't think you want to store multiple values in a single column in the jobs table; this calls for what is known as a junction or many-to-many table.

CREATE TABLE dbo.Jobs
(
  JobID             INT PRIMARY KEY,
  JobTitle          VARCHAR(255),
  JobDescription    NVARCHAR(4000),
  MinimumExperience TINYINT,
  MaximumExperience TINYINT,
  LastDate          DATETIME, -- what is this? Needs a better name I think
  InterviewDate     DATETIME, -- is this really a property of the job?
  ProjectID         INT, -- foreign key references dbo.Projects?
  HiringManagerID   INT, -- foreign key references Employees table?
  InterviewerID     INT  -- foreign key references something?
);

(Assuming there is only one interviewer. If there are multiple, consider a many-to-many table.)

CREATE TABLE dbo.Skills
(
  SkillID SMALLINT PRIMARY KEY,
  SkillName VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE dbo.JobSkills
(
  JobID INT NOT NULL FOREIGN KEY REFERENCES dbo.Jobs(JobID),
  SkillID SMALLINT NOT NULL FOREIGN KEY REFERENCES dbo.Skills(SkillID),
  IsPrimary BIT NOT NULL DEFAULT 1
);
Improve this answer
0
  1. 3 Columns (Primary Skills, Secondary Skills and Interviewer) should be removed from Master Table.
  2. New Table should be created (X Table).
  3. In this X Table Map the Id's of all the 4 table (i.e. Master Table, PrimarySkill Table, SecondarySkill Table and Interviewer Table) should be entered and mapped.

Also it would be good benefit while retrieving the data through Select Query.

Improve this answer
2
  • how can i map, if person have multiple skills. in the x table Commented Feb 25, 2014 at 10:48
  • In X Table - 4 columns having different IDs coming from 4 tables. If ID-1 from Master Table - 1 Primary Skill other column-row will be NULL, 3 Secondary Skill others will be NULL and 4 interviewer ids mapped with specific Primary / Secondary skill then it should be mapped likewise Commented Feb 25, 2014 at 10:53

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.