Hi, I am new to databases and I'm having some trouble. I've attached a diagram so you can see what I'm talking about:

http://i20.photobucket.com/albums/b2...o/database.jpg

What I'm trying to do is create a database with employees, their certifications, the organizations the certifications come from, and so on. One employee can have many certifications, one certification can be held by many employees, etc. I've done some searching online and I'm confused - do I need seperate tables to hold these relationships? Don't keys point to tables (i.e. that the empID FK in the certification table points to a number of employees?).

Say I want to start simpler, just employee and certification: name of employee in one and certification name in the other. How can I set up many to many relationships between them, so that I can look up the employee and come up with all the certs held by him?

Thanks,
James

You're thinking too hard. What does a certification consist of? Well, a title, a governing organization and perhaps a description? What does an employee consist of? An employee id, basic name/contact information and perhaps a title. How do you put those together? EmployeeCertifications, containing a certification id and an employee id.

Well that makes sense, but I believe that I would need an entire table of certifications. Well, maybe not. This is to be backend to an application, and the user needs to select the certification from a dropdown I would be calling from a database, the certification table. So maybe keep an entirely seperate table for all certifications on record, then copy that certification to a field for the employee?

Here's the thing though - I want to search by employee, certification, or organization, or any combination. So I think at the very least, the certification table and organization table would need to be related, right? So I can say that Microsoft points to MCSE/MCSD/etc and IBM to WebSphere/Modeler/etc, and the certificates should point back to the organization that gives them. Or just a table with all certs and a field with the company?

If I searched by user "James F" and organization "IBM" I want all IBM certs that I have.

I'm new to database construction, and the help I find is over my head to a certain extent. Maybe seperate tables then just compare the values?

I want to make this database clear, easy to maintain and upgrade, and fast and well thought out. That's why all the debating! I haven't designed one before

Thanks,
James

What you described can be done with four tables: Employee, certification, organization and employeecertification

Take a crack at figuring out how to link 'em up...

Teddy, here's what I've come up with.
Ignore the other tables, they are just exercises in the same fashion

Thanks for your help,
James

Oh, and if that's right, if I want to have a new employee, a new certification, and a relationship between them, what do I change? I assume I create the new employee, the new certification - but then the foreign and primary keys in the Emp_Cert table... are they set automatically?

Looks good to me, barring all those exam tables of course.

I'm not sure I understand your question with regard to new employees and certifications.

I'm just confused as to how the relationship table works. For each employee/certification relationship, do I need a unique identifier in the Emp_Cert table? So if Employee A has 10 certificates, there are ten rows in the Emp_Cert table, each with a unique id and pointing to the respective certifications and employee? That would, I assume, be the table I search.

Thanks,
James

Yup, you've pretty well got it. Standard junction table.

Thanks, my man! Big help!