We have answer of your question!

100% solved queries, no empty question

0

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?

Question author Kodkod | Source

Answer


1


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

Don't prefix your tables with tbl

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.

Related Search Queries:

how to perform deployment of a cloud service , how to perform deployment of a cloud services ,


You may also add your answer

Thanks for contributing an answer to Tick Answer!