Avoiding duplicates in designing One to Many relationship

I went through m开发者_如何学编程any threads and couldn't figure it out. Sorry if this is a duplicate question. Consider the following setup.

1) Employee => (ID,Name)

2) Department => (ID,Name,location,Clerk,Accountant,Middle-manager,Group-manager,Regional-manager,Active)

Department can have many Clerks, Accountants, Middle-managers and so on. They are just employees from the Employee table. Need a better database schema (flexible like, adding up a new column as Divisional-Manager must be easy) for Department entity with NO data duplication, NO update anomalies and NO / less junction tables.

Thanks in advance! :)

You need something like this;

CREATE TABLE department(
    dept_id      int    NOT NULL,
    dept_name    char(10)    NULL,
    CONSTRAINT PK1 PRIMARY KEY NONCLUSTERED (dept_id)
)
go


CREATE TABLE department_employee(
    id         int    NOT NULL,
    dept_id    int    NOT NULL,
    emp_id     int    NOT NULL,
    CONSTRAINT PK3 PRIMARY KEY NONCLUSTERED (id)
)
go


CREATE TABLE employee(
    emp_id      int    NOT NULL,
    emp_name    char(10)    NULL,
    CONSTRAINT PK2 PRIMARY KEY NONCLUSTERED (emp_id)
)
go


ALTER TABLE department_employee ADD CONSTRAINT Refdepartment1 
    FOREIGN KEY (dept_id)
    REFERENCES department(dept_id)
go

ALTER TABLE department_employee ADD CONSTRAINT Refemployee2 
    FOREIGN KEY (emp_id)
    REFERENCES employee(emp_id)
go

You have a many-to-many relationship so you need a third association (junction) table - you can't avoid it.

DepartmentMember => (DepartmentId, EmployeeId, MembershipRole)

Why don't you want this?

Employee =>(ID,name, department_ID, position_ID, Active)
Position =>(ID, name, Active)
Department => (ID,Name,location,Active)

Department =>(ID,employeeID,location,active) Employee =>(EmployeeID,name, position)

I think that would be a much better way of organizing your tables. This assumes that active is a property of the department, else move it to the employee table.

Assuming an employee can only work in 1 department. IF not, then yes, you need a third table to avoid duplication

Employee

ID, Name, EmployeeType, DepartmentID
(pk on ID, EmployeeType)

Department

ID, Name, Active

Position/Title is very much contextual to Department. One can be a Regional-Manager in one department and can additionally takes Consultant position in another department.

Then , the department and the Employee is many-to-many. The Employee to the position is also many-to-many. If you need flexibility ,like adding a new title for a department , the junction tables are necessary. You cannot avoid it.

You can refer to the following Table structure for reference:

Employee 
-----------------------
EmployeeID (PK)
EmployeeName
Active

Department 
-------------------------
DepartmentID (PK)
DepartmenName
Location

Position 
----------------------------
PositionID (PK)
PositionDescription (eg.Clerk, Accountant etc)

EmployeePosition 
----------------------------
EmployeeID  (FK to Employee.EmployeeID )
DepartmentID (FK to Department.DepartmentID)
PositionID (FK to Position.PositionID )

If the Position/Title is fixed to Employee instead of Department.i.e. An employee who is clerk and can be in that position to one or many dept., how can we go about it?

Do you mean that in an extreme case , many employees can have their own special titles ? and they belong to many departments? If yes ,suppose a employee ID 123 has a special title called "The Special One" , and it belongs to the IT , Account and Sales department . You first create this title (i.e "The Special One" ) in the Position table and get the Position.PositionID.

Then you insert 3 records for Employee.EmployeeID 123 into EmployeePosition table using this Position.PositionID and the Department ID of IT , Account , Sales departments.