relationships as a table

I have the tables tblEmployees and tblProjects.
tblProjects has one column called Percentagetime, to hold the percentage of
time by an employee for a project.

My Question : Why should we introduce a 3rd table called
tblProjectsAndEmployees?
tblProjectsAndEmployees will have
1)EmployeeNumber(PrimaryKey in tblEmployees)
2)ProjectNumber(Primary key in tblProjects)
3)PercentageTime

Having only the tblEmployees and tblProjects is not sufficient?

Kind Regards,
Vinodh Kumar PVinodh Kumar P (vinodh@.here.com) writes:
> I have the tables tblEmployees and tblProjects. tblProjects has one
> column called Percentagetime, to hold the percentage of time by an
> employee for a project.
> My Question : Why should we introduce a 3rd table called
> tblProjectsAndEmployees?
> tblProjectsAndEmployees will have
> 1)EmployeeNumber(PrimaryKey in tblEmployees)
> 2)ProjectNumber(Primary key in tblProjects)
> 3)PercentageTime
> Having only the tblEmployees and tblProjects is not sufficient?

As long as an employee only works on one single project it is. Or a project
never can be staffed by more than one employee.

But in real life an employee works on more than project, and project have
several members. Then you have a many-to-many relation, and the typical
way to sort that out is to introduce a linking table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql