Solved

Database Releationships

Posted on 2014-11-20
9
133 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 48

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 48

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 48

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using a hta file with MSSQL agent to schedule a script job 4 39
SQL R 21 26
SQLCMD Syntax 2 14
Access form for login using active directory 6 21
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

861 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