Solved

Database Releationships

Posted on 2014-11-20
9
134 Views
Last Modified: 2014-11-21
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
0
Comment
Question by:TrustGroup-UAE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40454918
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 56 total points
ID: 40454956
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
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40455373
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Assisted Solution

by:mw 162
mw 162 earned 110 total points
ID: 40455374
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
 
LVL 49

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40455394
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 40455516
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
 

Assisted Solution

by:mw 162
mw 162 earned 110 total points
ID: 40456428
"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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 111 total points
ID: 40456444
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
 
LVL 9

Assisted Solution

by:davidanders
davidanders earned 56 total points
ID: 40457800
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question