SQL Server: M-2-M Relationship

DBA/Developer could use the following Transact-SQL statements to create the Project and ProjectManager tables and a junction table, ProjectXPM, to implement the many-to- many relationship:

CREATE TABLE Project (ProjectID int PRIMARY KEY,Description varchar(25),StartDate

datetime);

CREATE TABLE ProjectManager (PMID int PRIMARY KEY,LastName

varchar(30),FirstName varchar(30));

CREATE TABLE ProjectXPM (ProjectID int,PMID int,CONSTRAINT

PK_Project_PMPRIMARY KEY CLUSTERED (ProjectID, PMID),FOREIGN KEY
(ProjectID)
REFERENCES Project (ProjectID),
FOREIGN KEY (PMID)
REFERENCES ProjectManager (PMID));

Each row in the Project table represents a single project within the organization and includes the project's start date. Each row in the ProjectManager table represents a project manager who may be assigned to projects within the organization. Each row in the ProjectXPM table represents a project manager assigned to a specific project. The ProjectXPM table contains a composite primary key consisting of the combination of ProjectID and PMID. This ensures that the table may include multiple project managers for each project and multiple projects for each project manager. The data model in this scenario would resemble the following:

 

DBA/Developer should not create a one-to-one relationship between the Project entity and the ProjectManager entity. This data model would allow each project manager to be assigned to only one project and each project to be assigned only one project manager, but would not allow projects to have multiple project managers or allow project managers to manage multiple projects. DBA/Developer should not create a one-to-many relationship from the ProjectManager entity to the Project entity. This data model would allow each project manager to be assigned to multiple projects, but would only allow each project to be assigned a single project manager. DBA/Developer should not create a one-to-many relationship from the Project entity to the ProjectManager entity. This data model would allow each project to have one or more project managers, but would allow a project manager to be assigned to only one project.