Database design

#1 (**permalink**) 28-Jul-2006, 08:11 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10223-database-design.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10223

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

#2 (**permalink**) 28-Jul-2006, 08:11 AM

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.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10223

"Squid" wrote:

> 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10223
> 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?
>
>

#3 (**permalink**) 28-Jul-2006, 08:11 AM

I recommend the following:

tblPerson

tblSkills

tblCertifications
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10223

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-
--

"Squid" wrote in message
news:937D3022-F649-48EF-9787-AF9B16092B19@microsoft.com...
> I am creating an Access Database to track a workers skills and
> certifications. Currently it is a flat table:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10223
>
> 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?
>
>