Click here to Skip to main content
14,699,759 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
tblDepartmentMaster:
IP
Security
Legal
IOT


employeee select his departments from a multi select dropdownlist. this departments details need to be stored in tblEmpMaster.
how to manage this data in table tblEmpMaster(not yet designed).
please help here in designing the tblEmpMaster table.
note: i have somemore multiselect data like Roles,Geo.

What I have tried:

these table should be scalable for future column additions.
Posted
Updated 29-Oct-20 7:40am
Comments
Gerry Schmitz 29-Oct-20 12:00pm
   
What's the "key" to department? One doesn't store (dept) "details" in an (employee) master; just the key(s).

1 solution

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 ...

e.g.
create table tblDepartmentMaster
(
    deptId int identity(1,1),
    deptName nvarchar(100)
);
insert into tblDepartmentMaster (deptName) values
('IP'),
('Security'),
('Legal')
('IO');
Note that the contents of this table are now
deptID   deptName
1        IP
2        Security
3        Legal
4        IO

When you design your tblEmpMaster 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 ...
empId   deptId
7       2
7       3
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
Database Normalisation
   
Comments
   
Thanks for your detailed ans. your Ans has clarified the most of my doubts.
Geo column represents the business Geo details of that person handles like a person can take care of Europe, APAC, Middle East related business activities.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900