Database design

[Guest]

I am creating an Access Database to track a workers skills and
certifications. Currently it is a flat table:

SSN - Text
MemberName - Text
Address - Text
Concrete - Yes/No
Drywall - Yes/No
Bricklaying - Yes/No
OSHA 10 - Yes/No
Rigging - Yes/No
OSHA 30 - Yes/No

I thought normalizing it into 3 tables:
MemberInfo: SSN, MemberName, Address
Skills: Concrete, Drywall, Bricklaying
Certifications: OSHA 10, Rigging, OSHA 30

1 Member can have zero or several Skills
1 Member can have zero or several Certifications

I can't seem to get the relationships working. Any suggestions?

 

[Guest]

I see 5 tables minimum. Two of them are linking or bridging tables as a
member can have more than one skill and a skill can be had by more than one
member.

tblMemberInfo: MemPK , SSN, MemberName, Address, etc
- MemPK should be an autonumber and the primary key for the table. Do not
use the SSN for the PK. If you want, the SSN could be a unique index to
prevent duplicates.

tblMemberSkills: MemSkillPK, MemFK, SkillFK, txtNotes.
- MemSkillPK autonumber primary key.
- MemFK is the foreign key related to tblMemberInfo MemPK
- SkillFK is the foreign key related to tblSkills SkillPK

tblSkills: SkillPK, Skill
1 SkillsConcrete
2 Drywall
3 Bricklaying
Etc.

tblMemberCerts: MemCertPK, MemFK, CertFK, txtNotes.
- MemCertPK autonumber primary key.
- MemFK is the foreign key related to tblMemberInfo MemPK
- CertFK is the foreign key related to tblCertifications CertPK

tblCertifications: CertPK, Certification
1 OSHA 10
2 Rigging
3 OSHA 30
Etc.

 

[Guest]

I see 5 tables minimum. Two of them are linking or bridging tables as a
member can have more than one skill and a skill can be had by more than one
member.

tblMemberInfo: MemPK , SSN, MemberName, Address, etc
- MemPK should be an autonumber and the primary key for the table. Do not
use the SSN for the PK. If you want, the SSN could be a unique index to
prevent duplicates.

tblMemberSkills: MemSkillPK, MemFK, SkillFK, txtNotes.
- MemSkillPK autonumber primary key.
- MemFK is the foreign key related to tblMemberInfo MemPK
- SkillFK is the foreign key related to tblSkills SkillPK

tblSkills: SkillPK, Skill
1 SkillsConcrete
2 Drywall
3 Bricklaying
Etc.

tblMemberCerts: MemCertPK, MemFK, CertFK, txtNotes.
- MemCertPK autonumber primary key.
- MemFK is the foreign key related to tblMemberInfo MemPK
- CertFK is the foreign key related to tblCertifications CertPK

tblCertifications: CertPK, Certification
1 OSHA 10
2 Rigging
3 OSHA 30
Etc.

 

[Larry Daugherty]

I recommend the following:

tblPerson

tblSkills

tblCertifications

tblPersonSkill - the many side of one to many with tblPerson. One
record for each skill this person has

tblPersonCertification - as above, many side of one to many with
tblPerson
One record for each certification this person
has.

HTH

 

[Larry Daugherty]

I recommend the following:

tblPerson

tblSkills

tblCertifications

tblPersonSkill - the many side of one to many with tblPerson. One
record for each skill this person has

tblPersonCertification - as above, many side of one to many with
tblPerson
One record for each certification this person
has.

HTH