Setting up New "portion" of DB

[KristenD]

I am adding another portion to my database and since this is a little different than the other parts I created, I wanted to get some input from the experts.

I am adding Certifications to my database. I am currently tracking skills, OSHA, & Licenses for all employees. My current count for the Certification is around 45. I'm wondering if I should set this up like I did my Skills (look up table for skills that is connected by a one to many relationship to another table) or have all the info on one table?

What would your best practice be?

Thank you!!

 

[boblarson]

Do it like the skills.

tblCertifications
CertificationID (PK)
CertificationDescription

tblEmployeeCertifications
EmpCertID (PK)
EmployeeID
CertificationID
CertificationDate
CertificationExpiration

 

[KristenD]

Thank you!! I was leaning towards that but I was second guessing myself.

 

[KristenD]

tblCertifications
CertificationID
CertificationDescription

tblEmployeeCertifications
EmployeeID
CertificationID
CertificationDate
CertificationExpiration

I had originally had in tlbEmployeeCertifications listed like this

tblEmployeeCertifications
EmpCertID
EmpID
Certification ID
CertificationDate
CertificationExpiration

Was I wrong in thinking that I needed to have a PK that I could just do an autonumber for?

 

[boblarson]

You can use an autonumber and then set a multi-field index (see here for more about that: http://www.btabdevelopment.com/ts/mfi )

 

[KristenD]

I've figured out the indexing for the table, however I cannot get tblEmpCertifications to have a relationship with tblEmployee.

What am I doing wrong??

 

[boblarson]

I've figured out the indexing for the table, however I cannot get tblEmpCertifications to have a relationship with tblEmployee.

What am I doing wrong??

What is the PK field in tblEmpCertifications and what is the PK field in tblEmployee?

 

[KristenD]

The PK for both is EmployeeID.

So I think I would have to add the field EmpCertID and use the indexing there rather than on the original field of EmployeeID so I can create a one to many relationship between the tables?

 

[boblarson]

The PK for both is EmployeeID.

So I think I would have to add the field EmpCertID and use the indexing there rather than on the original field of EmployeeID so I can create a one to many relationship between the tables?

Yes, you need to have an EmpCertID (see my original response which showed that EDIT: Actually I guess I fell down on that one.). You do not join (under most circumstances) a PK with a PK. It is PK --> FK.

 

[KristenD]

Ok, thank you...working much better now!