Table RelationShips

D

DS

Each Employee can have many jobs
Each Dept can have many jobs

So....I have this

tblEmp
EmployeeID
EmployeeName

tblJobs
JobID
JobName

tblDepts
DeptID
DeptName

tblEmpJob
EmployeeID
JobID

So I connect tblEmp to tblEmpJob
tblEmpJob to tblJobs and
tblJobs to tblDepts

What I need is a One on One relationship between Depts and Jobs but
Access says that this isn't correct. Is there another way to set this
up? Another table or field perhaps?
Thanks
DS
 
J

J. Goddard

Since Job to Department is one-to-one, a job belongs to a Department;
include the DeptId as a FK field in tblJobs.

John
 
G

Guest

DS said:
...
Each Dept can have many jobs
...
What I need is a One on One relationship between Depts and Jobs...

These two statements are contradictory. The first states that Depts --> Jobs
is one-to-many. So which is it? If in fact D --> J is 1:1, then why separate
them into 2 tables?
 
D

DS

J. Goddard said:
Since Job to Department is one-to-one, a job belongs to a Department;
include the DeptId as a FK field in tblJobs.

John
OK but...
Each Dept has many Jobs
Each Employee has many Jobs

So I would connect tblEmployee to tblEmpJob and
tblEmpJob to tblJobs and
tblJobs to tblDept

and of course the tblEmpJob would have both EmployeeID,
JobID and DeptID.

Does this sound Normalized to you?

Thanks
DS
 
J

J. Goddard

Sorry, I missed that one. A department can have many jobs, OK. But a
job can be in only one department. That makes Dept to Job one-to-many.
You cannot have a one-to-one as well.

You still should put DeptID in the Jobs table, but not in the table
tblEmpJob.

You did not supply quite enough information. Can one job have many
employees performing it?

If the answer is NO, you don't need the tblEmpJob table - just put the
EmployeeID into tblJob, so you have:


tblEmployee --> tblJob is one-to-many
tblDepts --> tblJob is one to many

If the answer is YES, then you need the tblEmpJob, and you have:

tblEmployee --> tblEmpJob is one to many
tblJob --> tblEmpJob is one to many
tblDepts --> tblJob is one to many

DeptID is not in tblEmpJob

There are no one-to-ones relationships in either case.

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top