Is my structure stable?

[Helystra]

After asking many questions all over the place and reading Roger's Access blog, I think I have finalized by new database design. I've used Roger's 12-steps to better databases to lay out my structure and I'm hoping someone can give it a look and make sure it's a sound and stable design or if I need to make changes.



I have attached the Word file 12-Steps to Better Databases - FS Database.zip for review. All details including the narrative and business rules are included in the file.

Thanks in advance

[Dal Jeanis]

Really thorough job. Very well done.

Here's some comments. When I say "Consider" something, I mean exactly that. Look at the idea, decide whether it makes sense, and accept or reject it, or use it as a springboard to change something else. You're the expert on your data and how it will be maintained and used.

In general, I'd suggest streamlining your design to eliminate as many tables as possible.

1) GREEN LIGHT. Consider merging the certification tables (EmpSani and EmpTASN). It's an arbitrary decision, but it's often easier to have a single table with two different examples of the same kind of information, rather than a separate table for each kind.

2) YELLOW LIGHT. The relationships between JobTitles, Schools and Employees is maintained through two tables, Schooljobs and AssignStaff. The Budget Code is assigned to the JobTitle. Something looks slightly off about that. Are all Schools really using the same budget codes for the same JobTitle?

3) RED LIGHT. There is a wagesEmp record and a WagesJob record. There is a huge loop from WagesEmp through employees, assignstaff, schooljobs, jobtitles, and wagesjob. This loop is a sign that you could write the same query two different ways and get two different results.

Consider setting up the database so that the Employee only acquires his wages by virtue of the jobs that he is assigned to, not directly. This will eliminate the loop and its potential for introducing errors. I believe that correcting this issue will also fix issue two, above.

For instance, what would happen if you eliminated WagesEmp and WagesJob and have Wages link directly to Schooljobs?

[ssanfu]

Yes, very nice job.

I would add: Watch out for reserved words (in RED)
They can/will cause you headaches.

Problem names and reserved words in Access: http://allenbrowne.com/AppIssueBadWord.html


Employees
Employeenumber
LastName
FirstName
MI
Status
StaffType
Address
City
State


Schools
CampusID
Name
Type
Address
City
State

Reviews
ReviewType
Date
Score


BudgetCodes
CodeName
Fund
Function
Object
OTObject
SubObject
OTSubObject
Org
Year

Schedules
ScheduleType
Name
Description

SchoolReviews
ReviewID
*SchoolID
ReviewType
Date

[Helystra]

Thanks so much both of you for your input.

Ssanfu: I will go through those reserved words and make some changes. I didn't even know that would be an issue. Thanks for catching that.

Dal Jeanis: Your idea about the certifications is a solid one. I had them on one table and then I thought maybe I should split them since they were two different "subjects" but you make a good case for putting them back together so I think I'll do that.

I see your points about the budget codes and wages and to be honest I had a terrible time with those simply because of the way both of those things work in the business rules. In fact I had all the rest done for quite some time and it's taken me 2 or 3 months to sort those two points. I will say that due to my limited diagramming skills, the WagesEmp loop you refer to isn't actually one big loop. They are several smaller self-contained loops. For instance the Employees table joins with the Wages table to make WagesEmp and that ends there. Wages and Job Titles join to make WagesJob and that's the end of that loop. JobTitles and Schools make SchoolJobs and that's the end of that loop. Then SchoolJobs joins with Employees to AssignStaff. And the Employees join up with Schools to assignsubtraining. I'm sorry the diagram wasn't as clear as it could have been.

That said, I'm very open to any suggestions you may have. Let me explain a little more about how those work because I think you may be able to offer a better solution with a little more information. The budget codes have 8 segments which change based on different factors. Fund, Function, Object, SubObject, Org and Year. The ORG segment is dependent on which campus it's being used for. The object and subobject have 1 number for regular wages and another number for overtime wages. The other segments are dependent on the job title. My thought process here was to assign the budget code to the job title and have the second column for both the Object and Subobject so that I could account for the change for overtime pay. Then leave the ORG segment blank to be filled in via a calculation (=campusID) within the reports and queries that pull the budget code. If there is a better way I can do this, I'm all ears.

The wages are a very tricky thing. All permanent full time kitchen staff have a "contract" wage which is dependent on the employee and not the job. This wages is used for all the different jobs they can do during the regular school year. The substitutes all get paid the same rate period. In the summer, we have 2 different wages. One for all lead cooks and one for all assistant cooks. And to further bake your noodle...The wage rate for assistant summer cooks and substitutes is the same.
It's worth noting here that employees change schooljob assignments throughout the year and their wages need to follow them rather than the job which is why I thought it better to attach the wages to the employee directly rather than to schooljobs. But I'm definitely an Access newbie so I maybe all backwards on that thinking.

[Dal Jeanis]

Hmmm. Let me step back a moment. Is there ever a person who is being paid two different wages for two different parts of their job? If not, then the wages are tied to the person, period. The summer-time and school-year rates may be different for the same person, but you don't really need to link the wages to anything else.

When I talk about a loop in the database architecture, I mean there are two valid ways to get the same information, which can result in different results based upon which way the query is coded. In this case, the wage for an employee can be calculated by going directly through the WagesEmp to wages, or by going through the AssignStaff to schooljobs to Jobtitles to Wages route. If you're not tracking actual payroll, I would be tempted to just have two fields on the employee record, schoolyearwage and summerwage, or just have a WagesEmp table that is linked to the employee but NOT architecturally linked to the Wages table. Remember, you can enforce relationships through application design as well as through database design.

As long as the "WagesJob" table is only theoretical information, and won't get accidentally treated as directly related to employee wages, then the design is valid. Just consider your alternatives and make your best decision. No design is perfect, so you aim for "optimal within my known usage constraints."

Clearly, you've done a good job thinking through your business needs, and you've proactively sought feedback before proceeding. I expect that any decision you make will turn out just fine.

[Helystra]

After reading through what you've written I see how I could have Regular, Summer, Differential and Substitute wage types on the WagesEmp table and then I think I can do away with WageJobs table. My only concern is having a lot of redundant data with the subs all having the same wages. But if you don't think that will be a problem then I'll go that route.

Thank you so much for helping me work that out.

[Helystra]

Now about those budget codes... Do you think I should move them to the SchoolJobs table given how they work or leave them as I have them?

[Dal Jeanis]

You're welcome. I suspected the wage attribution schema was over-complex.

About the budget codes - can they be calculated based on all the other factors? Typically, we don't store calculated items, but in accounting and budget applications we sometimes do, depending.

Let me ask a more basic question: What will you be doing with the data that comes out of this application? Is this application storing past expenses, or just forecasting? In a single simple sentence (each), what are the three mst important functions for the application?

[Helystra]

Actually this data is being stored as the employee payroll information template which will be exported into Excel for use with an already establish manual (Excel) payroll file. The calculations won't be done in Access. The excel payroll file has all the formulas built in, all that's needed is employee information and hours to be put into it. Up to this point it's been a monstrous activity having to update the excel file in all the right places when someone changes a work assignment or a pay rate etc or moves from being a sub to a kitchen staff employee.

[Helystra]

Here's a thought...If I do away with the budget codes table and add the 8 segments of the code as columns to the schooljobs table then I can fill in the ORG segment with the campus ID then and there...no need to leave it blank and fill in later. I could use the list value option in the other segments to restrict what can be entered in those fields to only the corresponding numbers that will be needed. It may end up with a lot of redundant data as the same values will be repeated within the columns but it does away with having to deal with a separate table and another relationship.

What do you think?

[Dal Jeanis]

Okay, that is critical information. This application is the data source for your live payroll process.

Yeppers, got to think it through. Hmmmm... Okay, the next step is to do a rudimentary data flow diagram. Map each piece of data that needs to exist to feed the payroll spreadsheet. Make sure you know where each piece is coming from, and see how it is being created and maintained.

Hard to make a general statement about this, since it's so process-specific. Think in terms of the process -- tell yourself a story about the users, otherwise called a "use case". If it's clear in your head, then good. If not, then tell it again... explain it to a convenient six-year-old or potted plant. (This is one of my professional secrets... potted plants are very good listeners, never interrupt your thought flow, and only ask the most significant questions.)

[Helystra]

I usually use my dog as my sounding board. She's a fantastic listener and very enthusiastic about whatever I'm saying. I also get the lick of approval when I'm done speaking which is an added bonus.

My users are my biggest problem. If the User Interface part of this isn't as easy as pie for them...it's all going to go south for the winter. That's why I had the budget codes on their own table so all they had to do was pick the code name if they ever had to enter a new job title. Which should be pretty darned easy for them. Then I'd just have the report to be exported fill in the ORG segment with the campus ID at the time it's generated.

I am also concerned about my users botching assignments when they have to move someone from one position to another. I'll have to figure that out in the form itself so that we don't end up with the same employee assigned to 2 different schools for the same job.

[Dal Jeanis]

Yep. Getting the workflow right is the acid test for your design. If your design supports a simplified workflow, then it's a workable design. If the user has to understand the design to do their job, it's a bad bad bad design.

If an employee can't be assigned to 2 different schools, then your form should be able to control that issue pretty easily.

[Helystra]

So here's a birdseye view.

There are almost 800 employees. Among them are Principals, LED(asst superintendents), custodial supervisors, area supervisors, kitchen staff and substitutes. I have one employee named "Vacant" to use in empty jobs. Substitutes are not considered kitchen staff for the purpose of the database. They don't have a contract and are assigned to the central office "school".

There are 88 schools with cafeterias and each one of them have their own set number of each job title. Example: Arnold elementary may have 6 job slots and Poe Middle school may have 8 slots with various job titles. I have 6 satellite campuses. These are campuses where the kids are fed from one of the other school's cafeterias. Example: Lanier High School cafeteria feeds the kids at Bowie learning center. Then there is 1 "school" which is actually the main office. This "school" is where the concessions, manager interns, substitutes and the random floating manager jobs are tied.

A normal contract for a kitchen staff employee covers breakfast and lunch during the regular school year and has a pay rate that is tied to the employee. Summer school is outside of the normal contract. There are only 2 pay rates for summer school regardless of the employees normal contract wage. One rate for the Lead Cook and one for the assistant cook (which happens to be the same rate as all the substitutes). Manager Interns are going through a manager training program and sometimes fill in for the manager at a school. When they do this, they get a differential rate to compensate for doing the job of the manager for the day. Manager Interns are the only ones that get differential pay.

Some schools serve an After School Meal which serves supper to kids who stay late for various school programs. These programs have sponsors like the Boys and Girls Club and a funding source which tells us how many kids they will cover "allocate" and then we also have to track how many kids are actually enrolled. This tells us how many meals to plan for as well as how man meals SAISD will have to pay for if there are more kids enrolled then the funding source allocates. This program requires one of the kitchen staff employees to work as lead cook and if there are a large number of enrolled kids, they will also need an assistant cook or two. Their pay rate is the same as their normal contract pay rate. Sometimes a substitute will need to fill in for one of the assigned cooks.

Anyone can work concessions. Kitchen Staff or Substitutes. Their pay rate for this is whatever their normal pay is.

I have 4 basic budget codes. One for the contract jobs, one for concessions, one for the supper program jobs, and one for differential pay. The Fund and Function segments are the same for all but concessions. The Object and Subobject depend on if it's regular or overtime rate. Then of course the Org segment is the same as the campusID and the year only changes when the school year does.

Does that make it about as clear as mud? LOL

[Dal Jeanis]

I believe that your knowledge of details may be interfering slightly with your clarity.

Pull out as many of the details as possible and deal only with the necessary abstractions. Often, you'll find that once you move to the abstract level, the design simplifies itself automatically. It's trying to build in these niggling little current-life relationships that over-complicates things.

Let's start with satellite campuses. Do they have their own employees (the people who transfer the food from campus to campus) or are they an aggregate "customer" of the providing school? Whichever way it is, that's a business requirement. (I'll have more questions when you answer.)

Let's continue with summer wages. What does it matter how many different kinds of wages there are in summer? It's not a design limitation, so don't use it for decision-making. There are different summer jobs and summer rates. Done.

The rate for assistant cook happens to be the same as all the substitutes. Useless trivia. Delete the statement from the requirements.

At least one type of position (manager intern) may have differential pay based upon daily activities (filling in for a manager). That's a business requirement. Plan for it without letting it limit later decisions. Two years from now you may decide to treat Lead Cook as "cook + daily shift differential", and the design will already support it.

About "Mr Vacant" - if a real employee can have multiple jobs, that's fine. If not, then you don't want a fake employee that can have multiple jobs. It will screw you up in the long run. Use Null for the Employee ID instead, or (possibly) use Null and a check-box to indicate unoccupied jobs. If so, then maybe a check-box on the employee record to indicate unreal/inactive staff or something like that. Be aware of how you're going to get your actual staffing level information.

The After school information is interesting, but the source of funding isn't relevant to design of staffing tables. Temporarily set aside that information while you finish the design of wage-employee-job. There is a potential position after hours. No rate difference. It's just a time slot.