Solved

Database Releationships

Posted on 2014-11-20
9
124 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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 45

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 45

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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

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 8

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now