small database doubt for my asp.net web app

[DotNetHunk]

hi guys!!i read all of da group messages,good to c this community
growing!!

i have a small doubt,pl clarify it soon guys!!!
see,i have to come up with a web application wherein i need to accept
the candidates details.now,the real problem is how shud i input the
skills of the candidate??in the web application,i shud search for
different candidates based on their skills provided.so how shud i best
implement a skill look up table??

i think i shud go for 3 tables::

table 1::
candidates

create table candidates
(
EmpId int primary key identity(1,1) not null,
name varchar(50) not null
)

table 2::
skills

create table skills
(
skillId int primary key identity(1,1) not null,
empId int foreign key references candidates(empId),
skillName varchar(50) not null
)

table 3::
EmpSkills

create TABLE EmpSkills
(
EmpId int REFERENCES Candidates(EmpId),
skillname varchar references skills(skillname),
SkillId int REFERENCES Skills(SkillId)
PRIMARY KEY (EmpId, SkillId)
)

this was the design recommended by an expert but he is unavailable
now.hope,some of u experienced guys will help me out at the earliest
with a design.

waiting guys...
kiran k

[Ravi]

I think there is no need of "empId" column in the skills table,

for getting input design a web form and bind the dropdownlist with skills table

[BabuLives]

Ya u can take off that empid in skills table..and one more thing why do
u want this skill name again in EmpSkills table??(its is redundant
na!!!)...

ok let us see ohers reply also..

Regards,
Satheesh

[Ravi]

yes we can take off skill name in EmpSkills table

[kiran konathala]

wow,thats a prompt reply guys.i jus posted that design for a clear picture.

> table 1::
> candidates
>
> create table candidates
> (
> EmpId int primary key identity(1,1) not null,
> name varchar(50) not null
> )
>
> table 2::
> skills
>
> create table skills
> (
> skillId int primary key identity(1,1) not null,

> skillName varchar(50) not null
> )
>
> table 3::
> EmpSkills
>
> create TABLE EmpSkills
> (
> EmpId int REFERENCES Candidates(EmpId),

> SkillId int REFERENCES Skills(SkillId)
> PRIMARY KEY (EmpId, SkillId)
> )

ok here is the suggested design.now how do i proceed??in my web app,i will offer a drop down list of predefined skills which the candidate can choose.say,he chooses upto 5 skills(5 dropdowns).now the skills r stored in the skill table right??but,in the skills table how can i relate those skills to a candidate when there is no emp id??im a novice still yaar,so pl help me out by giving me a clear picture.thanx one and all.

kiran k

---

Yahoo! FareChase - Search multiple travel sites in one click.

[sathiya subramanian]

Dear Kiran,

 

hope ur requirement is to display the employees based on skills right.

 

u can design ur table like this:

 

1.emp_table

emp_id(primary key)

emp_name

2.skill_table

skill_id(primary key)

skill_name

 

3.emp_skill_table

some_key(primary key-keep this auto generation number)

emp_id

skill_id

 

sample data:

 

emp_table

1 sathiya1

2 sathiya2

skill_table

1 dotnet

2 java

emp_skill_table

1 1 1

2 1 2

3 2 1

 

emp_skill_table says

that

1 record depicts sathiya1(1)  has skill dotnet(1)

2 record depicts sathiya2(1)  has skill java(2)

 

hope u can understand with the sample data.....

 

based on the skill selected....get the skill id and search in the emp_skill_table..u can get the different employees....

 

from this sample data we can get emp1,emp2 having skillset dotnet

 

try this out,

with regards,

sathiyajs
 

[BabuLives]

hai kiran...
Am proceeding with the assumption that the skills in the dropdown are
there in skills table.
Ur table EmpSkills have the relation na ie..
if in candidates table..

1 Kiran
2 RAM
---
---
---
Skills:
s1 C
s2 C++
---
---
---
Empskills
1 s1
2 s2
---
---
---
Like this...

You can get the empid from EmpSkills table after getting the skills
selected by the user...

Regards,
Satheesh

[kiran konathala]

wow,im beginning to understand ur design guys.both the views presented r very good.so,how and when is the emp_skills table generated??this is my only doubt now.

 

thanx

kiran

BabuLives <sathees...@gmail.com> wrote:

[BabuLives]

When u r giving i/p for employee skills u just update the table
empskills with empid and skillid..i think this is ur need...

Regards,
Satheesh

[kiran konathala]

yep,my doubt is cleared.now,ill try that and let u know guys.thanx for hlping me all of u!!(my next doubt will be like using stored procedures with ado.net,its really puzzling me yaar)!!

 

happy .netting!!

 

kiran k

[Siva CH]

Hi Kiran,

The following design will help you to work with good db design. Avoid working with IDENTITY keys when you are referencing them.

 

create table candidates
(
EmpId int primary key not null,

name varchar(50) not null
)

table 2::
skills

create table skills
(
   skillId int primary key not null,
   SkillName varchar(50) Not Null
}

table 3::
EmpSkills

create TABLE EmpSkills
(
EmpId int REFERENCES Candidates(EmpId),

SkillId int REFERENCES Skills(SkillId)
PRIMARY KEY (EmpId, SkillId)
)

Hope this helps.

Regards,

Siva 

 

[kiran konathala]

hi siva,thanx for da concern.but,in this scenario the candidate doesnt' have to register viz id and password.he just comes n fills out a form.

 

wat best approach can i implement??