Grouping table attributes

Question

0.00/5 (No votes)

See more:

Hi Everyone

I have 3 tables- Employeeskills(Emp_ID,Skill_ID) Teams(Team_ID,Emp_ID,Skill_ID, Team_Name, Availability), JOBS(Job_ID, Team_ID, Date_Time, Location). now i need to group employees from the EmployeeSkills table to the Teams table, and then each Team must have all the skills like cleaning windows, dish-washing, flooring and bathroom cleaning. meaning i could have five employees under one team. but my problem is how do i group these EmployeeSkills under one team and so on?. because i need to assign each team to a job. im using SQLServer and ASP.net

Posted 16-Aug-12 2:33am

Xavi Giqwa

Add a Solution

Comments

Zoltán Zörgő 16-Aug-12 8:55am

Do you have only one team configuration rule, or you need customizable rule sets, where you can specify how many team members having a specific skill has to be in a specific team? Do you want to gather team members automatically, or you want just to check if a team is conforming to a rule?

Xavi Giqwa 16-Aug-12 9:33am

I need customizable rule sets, where i can specify how many team members having a specific skill has to be in a specific team. for example if member A,B,C,D belong to team A, they must have different skills but must have all the cleaning skills together in their team so that i can assign them to a job and if they are not available i should be able to see that and assign another team to that job.

Zoltán Zörgő 16-Aug-12 9:48am

Well, this cannot be deduced from your original post :(
Another question: you have fixed teams, or you need to build teams ad-hoc based on their momentary schedule?

I am not asking these questions so I can make your job. I am asking them to guide you towards the entities you need to keep in mind when designing your business logic and data model. This is a complex project, nobody will give you more detailed answers unless you ask technical questions about specific problems, after you struggled yourself resolving it. So far this is an architectural and design task, not a coding one.

Xavi Giqwa 16-Aug-12 9:56am

I dnt have fixed teams already but i have the EmployeeSkill table wich means each employee has already been assigne a skill but i need to group these in one team and create many teams in the Teams table

Zoltán Zörgő 16-Aug-12 12:49pm

You need additional tables:
- Rules(Rule_ID, RuleName)
- RuleSkills(Rule_ID, Skill_ID, HeadCount)
If you don't have fixed teams, you don't need Teams table, only a JobAssignment(Job_ID, Emp_ID)
And you need Duration and a Rule_ID in the Job table.
I suppose an employee can have several skills, but you need to handle the overlapping - this depends absolutely
to the business rules you need to conform.

First create the rules: how many people with specific skills are needed for a specific kind of job?
When you create a job, you select the rule and set the starting time and duration first. Based on this, you can retrieve the necessary skill/headcount definition from the RuleSkills table. Than you only need to look for people with these skills that are not assigned to any job in the specific time frame. If you have gathered all, you have a team for that job.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aadhar Joshi · Answer 1 · 2012-10-18T18:16:00

Solution 1

Can u please brief your question i mean produce output u need here?

Posted 18-Oct-12 18:16pm

Aadhar Joshi