I have been to Roger's blog and have been making my way through a rather complicated database build. Using the Entity Diagramming, I have managed to sort out all but one last issue with my database design. I will copy my narrative here and hopefully someone can help me sort out the issue i'm having with joining multiple wage types, job titles, schools and budget codes and employees.
FOOD SERVICE DATABASE ENTITY DIAGRAMMING NARRATIVE – Entities and Attributes
Employees (empID, lastname, firstname, DoB, HireDate, status and staff type)
Schools (campusID, School name, type, address, city, state, zip, main phone, cafeteria phone, and fax)
Wages (employee-foreign key, wagetype, regularrate, overtimerate)
budget code (name, fund, function, object, OTobject, sub object, OTsub object, org, year)
job title (title, hours, jobcode)
All tables also have an autonumber field as their PK.
For Assignments I need to join the following information: employee, school, job title, wages, and budget code
Each kitchen, substitute, central office and warehouse stafftype employee may have multiple wagetypes (contract, differential, sub, lead and assistant - these are determined by the job title). Contract and Differential wagetype rates are different for all but the substitute employees. However, each other wagetype will be the same across multiple employees. (subs and assistants are all X reg and Y OT, Leads are all A reg and B OT)
A school has multiple job titles and a vacancy or employee for each job title. A job title may be assigned multiple times to the same school as well as to multiple schools. An employee may be assigned to multiple job titles at the same school or across multiple schools. A budget code is determined by wage type and may be assigned to multiple employees, job titles and schools. There is one budget code for regular pay and one for overtime pay. the only difference between them is the Object and the Sub object.
And this is where I'm stuck...I'm thinking that I need to join up the budget codes, wages and job titles by wagetype since that seems to be the common factor between the three of them. However, I have been told that once I relate a table to another I shouldn't do it again directly or indirectly. And the employees are already related to the wages so wouldn't that indirectly relate the employees again once I put it all together in tblassignments? Also, I think it's rather redundant to store so many different records to track the same pay rate for substitutes, assistants and leads and I would like to be able to just have the set rate for assistants pull up when I assign an assistant job title to a school regardless of what employee is assigned. I know there must be a way, but for the life of me I can't see it.
Ideas?