Database design


How you design the database schema for many to many relationship,for example
employee and department has many to many relationships?how many and how you will design tables?


consider the employee and department tables below the business rule is :
one employee can be assigned to multiple department and one department can be supported by multiple employees.Therefore there is need to create an M:M relationship to link these two tables
Employee_ID First_Name Last_Name
12 Sam Newton
15 Donald Duck

Dept_ID Dept_Name
10 Finance
20 Education
Employee and Department tables requiring an M:M relationship

To support the relational database model an M:M relationship must be resolved into 1:M relationship
figure illustrates this resolution with the creation of an associate table named EMPLOYEE_DEPARTMENT Table

      Primary Key      Employee	Primary Key          Department

First_Name Last_Name
Sam Newton
Donald Duck

Dept_ID Dept_Name
10 Finance
20 Education

Employee_Department Foreign Key
Employee_ID Dept_ID Assigned_Dept_Task
12 10 Department Management
15 10 DB Development
20 IT Development
In this example the associative table primary key- a composite of its employee_ID and Dept_ID columns- is foreign key linked to the tables for which it is resolving an M:M relationship. It reflects that one employee can be assigned to multiple department - and in this example, that one employee can be assigned multiple and different responsibilities for each department to which that person is assigned ,
NOTE that the employee with an EMPLOYEE_ID value of 15 is assigned to the two department but that his responsibilities are different for each department