I need help with database designing.
So far I have three tables:
TblDepartments -------------- DeptID (PK) DeptName TblSections ----------- SectionID (PK) DeptID (FK) SectionName TblWorkers ---------- WorkerID (PK) WorkerName
There is a 1:N relationship between Departments and Sections (a department may have several sections, a section belongs to one department).
Now, a worker may have a role either at the section level or at the department level (i.e. have the same role in all sections of the department).
I'm not sure how I should define the Roles table. I came with this definition:
TblRoles -------- WorkerID (PK)(FK) DeptID (PK)(FK) SectionID (PK)(FK) RoleDesc
But I don't like this solution and I feel it's wrong. (Either DeptID or SectionID must be null, and SectionID is dependent on DeptID anyway).
Is there a better way to define the Roles table?
First, you'll need a table of just roles since you can't have repeating data.
roles ----- roleId name
Can a worker have more than one role?
workerRole ---------- workerId roleId
If a worker will only have a single role, just add it to your worker table
worker ------ workerId name roleId
If a role can belong to a section and department, then one table for each:
departmentRole sectionRole -------------- ----------- departmentId sectionId roleId roleId
Answer author Kermit
Tickanswer.com is providing the only single recommended solution of the question How to design a table of worker roles under the categories i.e sql-server-2008 , database-design , . Our team of experts filter the best solution for you.