Access relationships...

gareth.e

gareth.e

gareth.e

Permabanned
Joined
9 Oct 2006
Posts
2,819
How is this looking:

relationships.png


I haven't used access for a while...

It's a database for a phone shop... That sells contracts basically.

Is the employee qualifications area looking ok?

(nobody else I'm meant to be working with has even the faintest idea about access lol)
 
Last edited:
Could be made a little better.

Employee-table looks fine, the primary key on the EmployeeID is fine.
Qualifications-table could ideally do with a primary key on there which doesn't look it has.

And therefore the "employee-qualifications-table" could be simply a 2 field table containing multiple rows of EmployeeID's and qualificationID's.
 
~J~ said:
Could be made a little better.

Employee-table looks fine, the primary key on the EmployeeID is fine.
Qualifications-table could ideally do with a primary key on there which doesn't look it has.

And therefore the "employee-qualifications-table" could be simply a 2 field table containing multiple rows of EmployeeID's and qualificationID's.
Click to expand...

employee qualifications is primary key in "employee-qualifications-table"

should this be a one to one relationship between the tables?

employee-qualifications to employee-qualifications?
 
Yep, Employee table can have a one-to-many with the employee-qualifications table, but the employee-qualifications table would only pick out on a one-to-one from the qualifications table.

Looks good to go.
 
It probably should be 1 qualifications-table -> many employee-qualifications-table shouldn't it?

Otherwise you are going to have one qualification of each type for each employee.
Something like
Code: 
employee-qualificationid : 1
qualification-name : firstone
qualification-description: First qualification that everyone gets

employee-qualificationid : 2
qualification-name : firstone
qualification-description: First qualification that everyone gets

employee-qualificationid : 3
qualification-name : firstone
qualification-description: First qualification that everyone gets
so your employee-qualifications-table can have a one to one of
Code: 
EmployeeId: 123
employee-qualificationid : 1

EmployeeId: 234
employee-qualificationid : 2

EmployeeId: 345
employee-qualificationid : 3

with one to many you can have
Code: 
EmployeeId: 123
employee-qualificationid : 1

EmployeeId: 234
employee-qualificationid : 1

EmployeeId: 345
employee-qualificationid : 1
 
Last edited:
SimonCHere said:
It probably should be 1 qualifications-table -> many employee-qualifications-table shouldn't it?
Click to expand...

No, because there are countless qualifications and countless employees, so therefore you'd need an additional table of just INT fields to store all probable combinations.
 
~J~ said:
No, because there are countless qualifications and countless employees, so therefore you'd need an additional table of just INT fields to store all probable combinations.
Click to expand...

which you've already got in the
employee-qualifications-table
and you only need the combinations that actually happen

employee 1 has qualifications 1,2,5
employee 2 has qualifications 1,3,6

now there is no point in having qualification 1 in the qualification table twice.
hence you have to have the one to many from the qualifications to the link table.
 
What if they've multiple qualifications?

EmployeeID
100|Adam
87|Bob
32|Charlie

QualificationID
78|Physics
12|Chemistry
04|Math

EmployeeQualifications
100,78
100,12
87,04
32,78
32,12
32,04

Surely that would work out more streamlined and allow for a more expandable database.
 
Erm that's what I said? :)

If you had a 1-1 between Qualifications and EmployeeQualifications you would have to have multiples of the same qualification, one for each Employee who has it.

If you have 1-many between Qualifications and EmployeeQualifications many Employees can have the same Qualification.
 
Right I'm getting confused :p...........


What should I change the primary keys to in each table/ how should it look?

Surely in the employee qualifications table: employee-qualificationID is the primary key

and employee qualificationsID is also the primary key in the qualifications table?

:confused: which means access will say it's a one to one?

heeeeeeelp :p

please explain / shoe me how you believe it should look!
 
Last edited:
I'm slowly gonna back out on this one as I too am getting a little confused, I think Simon and I are on the same page only describing it differently but his use of the English language is far better than mine.

His

If you had a 1-1 between Qualifications and EmployeeQualifications you would have to have multiples of the same qualification, one for each Employee who has it.

If you have 1-many between Qualifications and EmployeeQualifications many Employees can have the same Qualification.
Click to expand...

is worded slightly better than my:

Employee-table looks fine, the primary key on the EmployeeID is fine.
Qualifications-table could ideally do with a primary key on there which doesn't look it has.

And therefore the "employee-qualifications-table" could be simply a 2 field table containing multiple rows of EmployeeID's and qualificationID's.
Click to expand...
 
Your keys are fine.
I suppose in the employee qualifications table you don't really get any primary keys, they are both foreign keys of the other 2 tables.

~J~ and me are arguing about the 1to1 relationship you've got! :D

Simon
 
I've update the relations.. So theres no primary keys in the employee-qualifications table

Does this look awesome now?

relationships3.png


Should be set to make forms now.. right? :)
 
You must log in or register to reply here.
Back
Top Bottom