Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database Releationships

Posted on 2014-11-20
9
Medium Priority
?
142 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 668 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 49

Accepted Solution

by:
Dale Fye earned 224 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 668 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
Technology Partners: 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!

 

Assisted Solution

by:mw 162
mw 162 earned 440 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 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 668 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 444 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 440 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 444 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 10

Assisted Solution

by:David Anders
David Anders earned 224 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

571 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