CODEWITHSUNDEEP
c#mysqlsqlsql-serverdatabase-design
nav
nav

Reputation: 509

Database Design for the following Scenario

I am working on creating a database schema for the following Requirements: Employees have certain attributes. These same attributes are found on jobs. What I need to be able to do is search for employees based on a given job's attributes. For example, "Find me all employees that match job id = 1 and have the same attributes for it". (vice Versa for Searching for all jobs for a given employee). Here is my database schema.

Job (
   jobId, 
   description
)

Employee(
  employeeId, 
  firstName, 
  lastName,
  startDate,
  email
)

Attribute(
   attributeId,
   description       
)

JobAttribute(
  jobId, (FK to Jobs.jobId)
  attributeId (FK to Attributes.attributeId)
)

EmployeeAttribute(
   employeeId, (FK to Employees.employeeId)
   attributeId (FK to Attributes.attributeId).       
)

Query for "Find me all employees that match job id = 1 and have the same attributes for it". would then look like :

SELECT DiSTINCT e.employeeId 
FROM Employee e
INNER JOIN EmployeeAttribute ea
ON e.attributeId = ea.AttributeId
and e.employeeId = ea.employeeId
WHERE ea.AttributeId In (
Select ja.attributeId 
FROM Job j
inner join JobAttribute ja
    on j.jobId = ja.jobId
inner join AttributeTable at
    on ja.attributeId = at.attributeId
where jobId = 1
)
order by e.startDate asc

The query would return all employees that have at least one attribute the same as the job.

How would the query look like if I need to return all employees that have ALL attributes assigned to them as per the jobs using this schema?

Is this good design for query performance. I also want to make this as flexible as possible to allow new attributes to be added with minimal code change.

Upvotes: 0

Views: 184

Answers (1)

Tony Stark
Tony Stark

Reputation: 771

Ok, your first query, "Find me all employees that match job id = 1 and have the same attributes for it", could be simpler as below

SELECT DISTINCT E.EmployeeId
FROM 
Employee E
INNER JOIN EmployeeAttribute EA ON (EA.EmpId = E.EmployeeId)
INNER JOIN JobAttribute JA ON (EA.AttributeId = JA.AttributeId)
WHERE AJ.JobId = 1

on your query on retrieving employees matching all attributes for a given job

SELECT EA.EmployeeId, COUNT(EA.EmployeeId)
FROM EmployeeAttribute EA
INNER JOIN JobAttribute JA on EA.AttributeId = JA.AttributeId
WHERE JA.JobId = @jobId
GROUP BY EA.EmployeeId
HAVING COUNT(EA.EmployeeId) >= (SELECT COUNT(AttributeId) FROM JobAttribute J where J.JobId = @jobId)

You may have to join with Employee master to get other details of the employee. Both the queries assume that there is a PK on the derived tables which is a combination of both FKs.

In terms of performance, use the query execution plan and define your indexes accordingly.

Upvotes: 1

Related Questions