Employee table --> 2 fields in Project Table

[jreynold]

As you will gather by my question, I'm a newbie trying to create a database for my company. We are a contracting firm and have many projects and many employees.

I have an Employee table with each employee and information.

EMPid(pk)
EMPfirstname
EMPlastname

I have a Project table with each project and information.

ProjectID(pk)
ProjectName
SupervisorEMP *Name of Employee supervising project
ManagerEMP *Name of Employee managing project

How can I keep one table of employees and be able to associate two different employees to each project? Some empoyees will supervise and manage a porject. Some employees will supervise on some projects and manage on others. I would like to be able to bring up each employee and see what projects they are supervising and managing.

Thank you in advance for your help!

 

[IGPCS]

well i will tell you what

delete supervisor and manger from the project table and create a third table called projectDetails and there you could have the 3 or more fields

**ProjectDetails
ProjectID *the id of the project in the prject tables
SupervisorEMP
ManagerEMP
Type *Supervisor or manager


like this you could have as many emplyees you want for each project

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[jreynold]

Ok... I created this table, but am still not quite getting how to associate the employees with this new table.

I set ProjectID as the PK of tblProjectDetails. I connected tblProjectDetails to tblProjects with 1-to-1 relationship. How do I connect tblEmployees to tblProjectDetails? If I try to connect EmpID to both SupervisorID and ManagerID it only lets me create one relationship. Do I need to enforce referential integrity?

What am I not getting? I'm a fairly intelligent person but this is making me feel real dumb

 

[IGPCS]

you are right that you are intelligent so you will catch it really quick

you cannot make the projectid in the table projectdetails PK because this is the tblProjects PK and its the field where it will be linked and if you make it pk you would only be able to create 1

and i made 1 mistake in the projectdetails table you only need 1 employee and then you have the type to choose if he is a manager or supervisor

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[jreynold]

Oh! I see... I guess my natural inclination was to make the Projects table a long list of everything in the project and not create another table.

Thanks for the help! I'll try to return the favor to someone sometime!

 

[IGPCS]

your welcome

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[lespaul]

jreynold, you should read the fundamentals document linked below for some basics on table design.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi