Database Releationships

Hi Experts,

Trust you are well?

I'm trying to get my head round Database Relationship's and struggling to figure it out. On the attached Drawing:

1. Employees Can only have 1 Job Titles but there are Many Job Titles and Many Employees
2. Employees Can only have 1 Language but there are Many Languages and Many Employees
3. Employees Can have Many Skills and there are Many Skills.

What type of relations should I use? How can I link them together? Do I need another table to link things?

Cheers
TME
Database.jpg
LVL 1
TrustGroup-UAEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Employees Can only have 1 Job Titles but there are Many Job Titles and Many Employees
One to many relationship (one employee has a job title and a job title can be held by many employees)

Employees Can only have 1 Language but there are Many Languages and Many Employees
Depends on what for you'll the language. If it's languages that an employee can talk should be many to many relationship. If it's the language that the employee want to receive information (mails, letters, ...) then should be one to many.

Employees Can have Many Skills and there are Many Skills
Definitely a many to many relationship. That can be achieved using a link table (table with two columns: EmployeeID and SkillID)
0
Dale FyeCommented:
Do you understand how to implement that in your database?

In the case of the JobTitle, since they can only have one job title, you would add a JobDescriptionID to table Employees.  Likewise, if they can only have one language (see Victor's comment), then you would add a LanguagesID field to the Employees table.  

See Victor's comment about how to actually implement the many-to-many relationship between Employees and Skills.

However, in many cases, with an employee database, you really don't want to put the JobDescription in the Employees table, you want an EmployeeJobs table that might contain fields:
EmployeeJobID
EmployeeID
JobDescID
JobStartDate
JobEndDate
SupervisorID

and any other fields that might be specific to that Employee/Job position.  If you include the SupervisorID in this table, you would have to provide a way to change the supervisor, which means you may have multiple records with different JobStartDate, JobEndDate, and SupervisorID field values for the same EmployeeID/JobDescriptionID field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
They are all potentially many-to-many relationships.
So, are you saying that in the first option, an employee can be Director and Secretary at same time?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mw 162SQL Server Consultant DBACommented:
The Employees and Skills tables are a many-to-many relationship. One employee can have many skills, and one skill can be held by many employees. A many-to-many relationship can be implemented using two one-to-many relationships. To implement, an intermediate table sits in between called "EmployeeSkills" with a compound unique key of EmployeeID and SkillsID.  The Employee table sits on the 1 side, and the EmployeeSkill sits on the Many side.  Likewise, the Skills table also sits on the 1 side, with the EmployeeSkills table on the many side.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First case is Employee - Job Title and is what I was referring to.
I agree with you in the Employee - Skills (3rd case) as you can see in my first comment.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Based on the presented requirements the following physical design should help...(Please double check the code)

create table employee (id int identity(1, 1),
lastname varchar(30) not null, 
firstname varchar(30) not null, 
dob date time not null, 
title int not null, 
language int not null,
primary key (last name, firstname),
foreign key title references titles(id),
foreign key language references language(name)
);

create table titles(
id int identity(1, 1),
name varchar(30), 
primary key (name)
);

create table languages(
name varchar(30), 
primary key (name)
);

create table skills_employee(
skill varchar(30) not null, 
employee int not null,
primary key (skill, employee),
foreign key employee references employee(id)
);

create index ir_employee_id on employee(id);
create index ir_employee_title on employee(title);
create index ir_employee_language on employee(language);
create index ir_title_id on title(id);
create index ir_language_id on title(id);

Open in new window


Hope this helps...
0
mw 162SQL Server Consultant DBACommented:
"They are all potentially many-to-many relationships.

So, are you saying that in the first option, an employee can be Director and Secretary at same time?"


Whilst an employee may not hold more than one job title at any one time, it is possible that they hold more than one job title over time. A change in role, or promotion would be an example of this.  In this case an employee may have many job titles over time, and a job title may be held may many employees.  In this example of a many-to-many relationship, the intermediary (link) table, which you might call EmployeeJobTitles would contain not only the compound unique key of EmployeeID and JobDescriptionID, but also additional information relating specifically to the combination of employee and job description e.g. Start Date and End Date.

It depends on what your requirements are.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
If an employee has only one position at the time, it is a one to many cardinality relationship is enough.  A many to many example is patient - options or patient - bed in a hospital.  During a stay, one patient may occupy many beds but a the same bed may be occupied by  multiple patients.

Hope this helps.
0
David AndersTechnician Commented:
Student >--- Attendance ---< Classes   A join table links Many Students to Many Classes.
ERD video is here
https://www.youtube.com/watch?v=-fQ-bRllhXc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.