Is my structure stable?

[Helystra]

Thanks. Yes I have a tendency to not see the forest for the trees sometimes. I have been working on this since the beginning of the summer so I have gotten bogged down in too many little details. Let's answer some of your questions and hopefully you can lend a machete to my intellectual thicket.

Satellite campuses don't really matter at all except that they exist and I need to know who feeds them. They have no kitchen staff employees of their own but they do have principals supervisors and other administrative staff that we just need to keep contact information for in case we need to reach someone. They also have feeding schedules which we need to be aware of. So while they don't factor into the wages part of things they do factor into other parts of the database.

I guess it doesn't matter how many different kinds of wages there are. If I'm going with wagesemp table as housing all the different wage types and their rates, then it makes no difference. I can sort that detail out in queries and reports later on. This fixes the next two parts of your post regarding assistant cooks and differential pay as well. Got it.

The vacancy is there because when the district sends us the list of the schools and their jobs and employees, they list the vacant positions. I could remove that employee and leave it blank but I fear that may confuse my users. Vacancy shows that the value is known and that it's a position that's not filled rather than someone just forgot to put an employee in there. I need to be able to print reports for my Operations director showing which schools actually have vacancies to fill. It's not got a lot to do with the staff and their wages but in assignments it's pretty important.

The funding and sponsors of the programs are kept on a separate table from the other things so it doesn't directly interact with the employees and wages. That was just additional information I gave you for the database as a whole. I probably didn't need to provide that for the wage problem. Sorry.

[Helystra]

sorry I double posted on accident.

[Dal Jeanis]

Don't apologize for gathering too much information. Your attention to detail is why you're good at your job, why you clearly deserve my professional respect, and why I'm taking the time to take you through every piece of the thought process that I can, point by point. My assessment is that you're the type of person that, later, will pay the effort forward to help someone else.

My pointing out the excess info was to help free you from unnecessary complications in your constraints. Stepping back and generalizing the types of relationships, rather than the specific values of relationships, should help you to achieve a simplified abstraction that can be coded and maintained with ease (or at least without undue difficulty). It's the finesse part of normalization - you can properly represent a relationship a dozen ways, and the more effective choices are often the more general, rather than the more specific.

Occasionally, the business requirements for a particular application may inspire selective denormalization of some tables, either to speed access to frequently accessed data (Arbitrary example: putting the full formatted employee names on their work history records so we don't have to always link back to the staff records and merge the parts of the name) or because fully normalized tables would be overkill for the application (arbitrary example: Keeping two phone number slots on the sales prospect record, rather than normalizing prospect phone numbers to a table of their own).

RE: VACANT - It might be better - not is, but might be - to leave those positions staffed by Nulls but create the forms so that they display as "Vacant", using a LEFT JOIN and the NZ function. Either way, that's an architecture/design point that I actually haven't had to deal with at all, and I don't see an obvious methodological advantage one way or the other. The good news is, it shouldn't be difficult to switch methods if the first one you try doesn't turn out to be convenient.

[Helystra]

I really appreciate all your help and your kind words. You have managed to help me sort out the last nagging bits of my design and now I'm off to start creating forms and inputting test data. I'm sure there will be a ton more coding questions and such as I go through application design but at least the structure is complete.

[Dal Jeanis]

Good job! I'm looking forward to hearing how it comes out.