CODEWITHSUNDEEP
mysqldatabase
Inno the tech geek
Inno the tech geek

Reputation: 385

Database design for user experiences with various specialities

enter image description here

I’m doing a database design for user experience with specialties. A user can have experience months/ years for each specialty ( please see the attached images) I’m planning to create a table to list all specialities, and a table to list experiences within years, and another table to list experiences with in months, and finally another table that will basically say “this user has this number of years / months experience for this speciality”

enter image description here

enter image description here

enter image description here

enter image description here

but having a table for experience is months and another table for experience in years kinda sounds redundant, but I can’t think of another way to do it. Is my design fine ?

Upvotes: 0

Views: 51

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Here's one idea:

User_ID Skill_ID Experience unit 
1.       101        7.      Year
1.       102.       4.      Month
2.       101.       9.      Month

Upvotes: 1

Tarek AS
Tarek AS

Reputation: 187

You can simply make a table candidate_experience which has the following columns :

  • candidate_id which indicates the id of the candidate
  • specialty indicates the name of the specialty
  • in_years indicates the number of experience in years
  • in_months indicates the number of experience in months

You can just make a single table. Without the need to do 4 separate tables.

P.S: You may need to make a composite primary key (candidate_id , specialty) to avoid duplication of same specialty for the same candidate.

^^ This is of course if you are sure that there is no multiple specialties with the same name. Else, you can make a separate specialties table as you did and replace specialty with the specialty_id in the candidate_experience table

Upvotes: 0

Related Questions