If an employee can be in multiple departments, don't store those details in the master table for the employee.
Use a "link" table that connects the two ...
create table tblDepartmentMaster
deptId int identity(1,1),
insert into tblDepartmentMaster (deptName) values
Note that the contents of this table are now
When you design your
table make sure that each employee also has a unique identifier e.g. empId (Name is not
a good identifier by the way)
Create a third table that contains one row for each department per employee containing the id of the department and the id of the employee. You might want to include From and To dates as well
E.g. Employee #7 is in the Security AND Legal departments so the link table would contain two rows for that employee ...
If the employee can only be in one department at a time, and if you don't want to record any movements between departments then simply include a column on your tblEmpMaster table to hold the department deptId. This is the more likely scenario to be honest
You would have link tables for any of the things where more than one could apply to a single employee - Roles might be an example of this. Geo is more likely to be a single column on the master table as you can't be in two places at the same time :laugh:
Key topics for you to research :
Foreign key relationships between tables