R
rocky20
I need some help laying out some table designs. We are asking our staff to
fill out what percentage of their time they spend on certain jobs and then
we want to categorize the staff based on their answers. Here's how it would
look.
1.Computers ___%
2.Monitors ___%
3.Printers ___%
4.O.S. ___%
5.Applications ___%
6.Content ___%
7.Phones ___%
8.Management ___%
Categories
1.Hardware - Sum of Computers,Monitors, and Printers at least 50%
2.Hardware Exclusive - Sum of Computers,Monitors, and Printers at least 90%
3.Software - Sum of O.S. and Applications at least 50%
4.Software Exclusive - Sum of O.S. and Applications at least 90%
5.Support - At least 25% in Hardware AND 25% in Software OR 25% in Phones
After I store the percentage data I need to calculate what category they fit
in.
Here is what I have so far.
Job Table (List of the jobs)
-Job_ID
-Job_Desc
Example:
Job_ID = 1 Job_Desc = Computers
etc...
----------------------------------------
Staff Job Table (Pct of time staff spends on specific job)
-Staff_ID
-Job_ID
-Pct
Example
Staff_ID=1234 Job_ID=1 Pct=60%
etc...
-----------------------------------------
Category Table (List of categories and the sum total needed to qualify)
-Category_ID
-Category_Desc
-Sum
Example
Category_ID=1 Category_Desc=Hardware Sum=50%
Category_ID=2 Category_Desc=Hardware Exclusive Sum=90%
etc..
---------------------------------------------
Job Category Table (Which jobs belong to which categories)
-Category_ID
-Job_ID
Example
Category_ID=1 Job_ID=1
Category_ID=1 Job_ID=2
Category_ID=1 Job_ID=3
Category_ID=2 Job_ID=4
etc...
----------------------------------------------
Staff Category Table (Which Staff belong to which Category)
-Staff_ID
-Category_ID
Example
Staff_ID=1234 Category_ID=1
etc...
---------------------------------------------
I think this would work except for the case of category 5 (Support At least
25% in Hardware AND 25% in Software OR 25% in Phones). How would I
represent that in the database. Is there a way to put all the requirements
into a table so all the logic can be done in the database. I don't want to
have to hard code the logic into my program.
Thanks
fill out what percentage of their time they spend on certain jobs and then
we want to categorize the staff based on their answers. Here's how it would
look.
1.Computers ___%
2.Monitors ___%
3.Printers ___%
4.O.S. ___%
5.Applications ___%
6.Content ___%
7.Phones ___%
8.Management ___%
Categories
1.Hardware - Sum of Computers,Monitors, and Printers at least 50%
2.Hardware Exclusive - Sum of Computers,Monitors, and Printers at least 90%
3.Software - Sum of O.S. and Applications at least 50%
4.Software Exclusive - Sum of O.S. and Applications at least 90%
5.Support - At least 25% in Hardware AND 25% in Software OR 25% in Phones
After I store the percentage data I need to calculate what category they fit
in.
Here is what I have so far.
Job Table (List of the jobs)
-Job_ID
-Job_Desc
Example:
Job_ID = 1 Job_Desc = Computers
etc...
----------------------------------------
Staff Job Table (Pct of time staff spends on specific job)
-Staff_ID
-Job_ID
-Pct
Example
Staff_ID=1234 Job_ID=1 Pct=60%
etc...
-----------------------------------------
Category Table (List of categories and the sum total needed to qualify)
-Category_ID
-Category_Desc
-Sum
Example
Category_ID=1 Category_Desc=Hardware Sum=50%
Category_ID=2 Category_Desc=Hardware Exclusive Sum=90%
etc..
---------------------------------------------
Job Category Table (Which jobs belong to which categories)
-Category_ID
-Job_ID
Example
Category_ID=1 Job_ID=1
Category_ID=1 Job_ID=2
Category_ID=1 Job_ID=3
Category_ID=2 Job_ID=4
etc...
----------------------------------------------
Staff Category Table (Which Staff belong to which Category)
-Staff_ID
-Category_ID
Example
Staff_ID=1234 Category_ID=1
etc...
---------------------------------------------
I think this would work except for the case of category 5 (Support At least
25% in Hardware AND 25% in Software OR 25% in Phones). How would I
represent that in the database. Is there a way to put all the requirements
into a table so all the logic can be done in the database. I don't want to
have to hard code the logic into my program.
Thanks