Structure of table with multiple foreign keys

[Paszt]

I'm creating a web app for managing projects. I have a users table with UserId (uniqueidentifier), FirstName, & LastName among other fields. Now I need to store data about the Project which will have an associated Project Manager, Sales Manager, Project Leader, Engineering Coordinator, and other users with specific roles in the project.
My query regards the structure of the database tables and having the ability to display the FirstName & LastName of the project's specific roles in place of the UserID.
Would it be best to have one 'Projects' table with ProjectID, ProjectName, ProjectManagerID, SalesManagerID, EngineeringCoordinatorID, ... (ProjectManagerID would be the UserID from the 'Users' table and the same for the other roles). OR should I normalize the database with a 'Projects' table (containing ProjectID & ProjectName) and a Project-ProjectsManager table (Containing ProjectID & UserID), Project-SalesManager table ... ?

 

[cjard]

The first way. If a project has a variable number of developers or other kind of user (in additional to the always-has-one-manager) then you'd have a column for developer group, and a middleman table breaking down the M:N (one project can have many developers, one developer can be on many projects)

If your project can have X managers, Y salespeople, Z whatevers, then yep youre gonna need middlemen tables for all those. welcome to multidimensional headaches!

 

[Paszt]

Thanks Cjard. My problem was with displaying the foreign key's value for each user role. I solved it by having all the UserIds (ProjectManagerID etc) in the project table and using a combination of views and stored procedures using multiple joins. For some reason SQL server didn't like all the joins in a stored procedure but it worked for a view.