Database design


#1

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?


#2

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
Employee_ID First_Name Last_Name
12 Sam Newton
15 Donald Duck

DEPARTMENT
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
15
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