Weekly Production Tracking Database

[tome20]

Hi all. I've been getting great information from this forum for a couple of years now, but I'm not having any luck finding a solution to a new problem I'm encountering in a database design.

So my team of approximately 40 people works on electronic order forms collaboratively via Sharepoint 2003. We have a requirement to provide a weekly report to our customer on how many orders each individual on the team has processed, and this performance metric is measured against how many hours they were at work.

In addition, we have multiple roles among the team members (initial request prioritization, secondary prioritization, order drafter, reviewer, 2nd reviewer), and team members may fill multiple roles over separate records (just never on the same record).

As of right now, I'm planning on building the following tables

- ID
--User ID
--Database access (0, 1, 2, 3, etc...; security to potentially be
implemented sometime in the future)

- Team
--User ID
--Team

- Hours
--User ID
--Week or date? (historically, we have just reported weekly numbers. Would we need to input daily numbers in order to build the queries? Should I worry about the additional size a table with 40+ users will have for 365 days as opposed to 52 weeks?)

- Record Status (to be imported from Sharepoint. These fields are not easily modified)
--Priority
--Date drafted
--date 1st review
--date 2nd review
--Drafter
--1st reviewer
--2nd reviewer
--customer
--record status (where it is in the queue)
--Number of forms
--Number of items
--supplier status (a, b, or c)
--supplier response (accepted, rejected)

One other issue I may have is that I expect to exceed 200,000 records by the end of the year, and I am limited to Access 2003 by our current enterprise build.

I appreciate any advice you all can give.

 

[jzwp22]

Access 2003 has a 2GB capacity and can handle 255 concurrent users. I'm not sure how much space 200,000 records will take up, but since you will have a multiuser application, it is imperative to split the application into a frontend database and a backend database. Your tables will be stored in the backend while the frontend will have your forms, queries, reports etc. This effectively gives you 4GB of space to work with.

Now to the tables:

You need a table to hold the members of your team (I assume you only have 1 team) and those team members are all users

tblUsers
-pkUserID primary key, autonumber
-txtFName
-txtLName
other user related fields

To record the number of hours worked in a week, you can continue to do that. I would set up a table that has the start date of each work week

tblWeeks
-pkWeekID primary key, autonumber
-dteWeekStart

Then you can join the team members to the work week so that they can enter the total hours worked for each week

tblUserHours
-pkUserHoursID primary key, autonumber
-fkUserID foreign key to tblUsers
-fkWeekID foreign key to tblWeeks
-hoursworked

As to this:

...how many orders each individual on the team has processed, and this performance metric is measured against how many hours they were at work

If multiple people work on an order how can you attribute a processed order to an individual?

Looking at the Sharepoint data, it is not a normalized structure, so you will have to extract the data from each import and put it into a normalized structure. The alternative to this is to do everything in Access.

 

[tome20]

Thanks for the response. As to your fist assumption, I'm afraid I do have several teams (4 teams with a total of 9 sub-teams right now, and we're expecting to grow in the next year)

The attribution of orders to persons is based on the step they completed (ie. if I process three order requests comprising 4 order forms with 40 items total, I would receive credit for those items separate from the 1st and 2nd reviewers.) Each step in the process counts as a unit of labor that is reportable to our client. So the data pull will count entries greater than null in a given date range.

Basically it's three different queries

- Processed; sum 'number of forms' if (date processed >date a, <date b)
- 1st review date; sum 'number of forms' if (date 1st review>date a, <date b)
- 2nd review date; sum 'number of forms' if (date 2nd review >date a, <date b)

Beyond that, I need to apply a formula that says something like; '1 hour at work is equal to 1.5 order forms processed'. Based on the hours, the report will also spit out a percentage over or under quota. (The production requirement varies based on which step in the process is being accomplished.)

I should have no problem getting those first three components into a single report. I'm more concerned with how to construct queries that produce meaningful metrics for individuals, teams, sub-teams, and/or the entire group. In order to do so, I need to be able to incorporate the hours worked. I'm just not sure of the construction of that table. I'm assuming you mean to have my rows listed as the start date of the week and for my columns rows to be user id and hours. How do I return a count of the hours combined with the first part of the query based only on date entry?

As to Sharepoint issues, I've established a view that only exports masked fields. The data is already normalized, so I shouldn't have any issues there.

 

[jzwp22]

Before worrying about queries, forms or reports you have to develop a normalized table structure. Here is a link that discusses what normalization is in reference to relational databases: normalization

The following table is not normalized as evidenced by the repeating fields date 1st review and date 2nd review along with 1st reviewer and 2nd reviewer.

- Record Status (to be imported from Sharepoint. These fields are not easily modified)
--Priority
--Date drafted
--date 1st review
--date 2nd review
--Drafter
--1st reviewer
--2nd reviewer
--customer
--record status (where it is in the queue)
--Number of forms
--Number of items
--supplier status (a, b, or c)
--supplier response (accepted, rejected)

To create the teams, you will need a table to hold the basic team info. You said that you have subteams so I would consider those as teams as well

tblTeams
-pkTeamID primary key, autonumber
-txtTeamName
-SubTeam (yes/no field, checked to designate a subteam, unchecked for a mater team)

To associate the subteams with their respective master team, you need a table like this:

tblTeamsAndSubteams
-pkTAndSID primary key, autonumber
-fkMTeamID foreign key to tblTeams
-fkSTeamID foreign key to tblTeams

Now, you need to associate the users to the subteam(s) they are on

tblTeamUsers
-pkTandSUserID primary key, autonumber
-fkTAndSID foreign key to tblTeamsAndSubteams
-fkUserID foreign key to tblUsers

It sounds like you have a series of steps that an order must go through and each has an associated unit of labor

tblTasks
-pkTaskID primary key, autonumber
-txtTaskName
-UnitLabor

If I were designing an order tracking database, I would have the following basic:

tblCustomers
-pkCustID primary key, autonumber
-txtCustomerName

tblCustomerOrders
-pkCustOrderID primary key, autonumber
-fkCustID foreign key to tblCustomers
-dteOrder (order date)

tblItems
-pkItemID primary key, autonumber
-txtItemDescription
-currPrice (item price)

tblOrderItems
-pkOItemID primary key, autonumber
-fkCustOrderID foreign key to tblCustomerOrders
-fkItemID foreign key to tblItems
-qty (quantity ordered)
-currunitprice (I would populate this field from currPrice of tblItems at the time of the order)

Now to track the order and the steps it has to go through, you would need a table like this

tblOrderTasks
-pkOrderTaskID primary key, autonumber
-fkCustOrderID foreign key to tblCustomerOrders
-fkTaskID foreign key to tblTask
-fkUserID foreign key to tblUsers (the person doing the task)
-dteTask date of the task


I'm not really sure what the imported Sharepoint data represents, but I would think Access would be able to handle it much more easily than Sharepoint.