Need help with complicated database design

[Helystra]

I am currently in the process of rebuilding a database (from scratch) that I had created as an Access noob and is already in use. I have learned a lot over the last few months but I'm still fairly new and don't understand much of what Access is capable of, so bare with me.

I have been told that look up fields in tables are a no-no and that I need to use a join table to marry information from multiple tables. Trouble is, I have no idea how to do that. So, I've come to ask for some guidance/input into my new design.

Let me explain the purpose of my database and the objects I need to track and how they interact with each other so it will be easier to understand what I'm trying to do. This database is for the food service department of a school district with 600+ Employees that work 6 different programs across 94 Schools. Right now I have 3 main tables: tblEmployees, tblPrograms, tblSchools and 3 support (feeder) tables: tblBudgetCodes, tblSchedules, tblJobTitles. I already know that I'm going to have to create at least 3 join tables (1 for substitute area assignments, 1 for schedules and schools and at least 1 for employee assignments to programs/schools/job titles/budget codes).

Here is an overview of the information I need to track, relate and join.

tblEmployees:
EmpID
Contact info (last name, first, mi, address, phones, emails, DoB etc)
Job info (hiredate, status, staff type (principal, kitchen staff etc), contracted pay rates, payroll comments)
Certification information (2 types of certificates. Sanitation and TASN, effective dates, expiration dates for both)
Uniform Sizes (shirts, pants, shoes-tracking type, size and qty for all uniform pieces and comments)
Improvement Plans (employee reviews - plan date, number of issues, followup dates, continuation dates and close dates as well as comments)
Substitute information (letter of intent date, orientation date, training date, eval date, #of evals, #of no shows, want full time?, areas they can work (1-4 or all), and comments. I have been told to join the sub empids with the sub areas on a new table.

*I suspect that I'm going to be instructed to break down the employees table into a few other support tables and join them up using the EmpID or something. Ending up with:
tblEmpContact
tblEmpJobInfo


tblEmpCertifications
tblEmpUniforms
tblEmpImpPlan
tblEmpSubs

tblSchools:
basic information (campus #, name, type, address, phone, fax etc)
Area (1, 2, 3 or 4 for substitute assignments)
Health Inspection dates (x2 per year)
kitchen evaluations dates (x2 per year)
OnSite Review Date (x1 per year)

*Staff - comes from tblEmployees (LED, principal-x1, area supervisor-x1, custodial supervisor-x1 & kitchen staff-xmultiple job titles) - will have to be assigned via join table. The kitchen staff can change depending on the program. Each school has it's own set number of each kitchen staff job title (1 manager, 1 possible assistant manager, # 7hour cooks, # 6.5hour cooks, # 6hour, # 5hour etc). Some of the schools have a supper program and some have a summer school program and their assignments are very different and even have different pay rates.

tblPrograms:
Type [Contract, PM Snack, ASM program, Concessions, Tutoring (all of those being at normal reg/ot pay rates by employee), Manager Intern (differential reg/ot pay rate by employee), Summer School (Program specific pay rates)]
Sponsor
FundSource
StartDate
EndDate
Allocation
Enrolled
MonitoringDates (up to 3x per school year depending on the program)
*School (will need to be joined to tblSchools)
*BudgetCode Regular (from tblbudgetcodes)
*BudgetCode Overtime (from tblbudgetcodes)
*Each school has programs with their own sponsors, fund sources and monitoring dates number of allocated students and number of students enrolled. Not all schools will have all programs. Summer school has it's own pay rate (which I will have to add fields for somewhere I'm just not sure where) since it's outside the contracted school year (lead cooks at x/hr and Assistant cooks at x/hour). There needs to be a separate budget code for regular and OT as the code changes depending on which pay rate is being used. This will be used in reports that are printed for payroll later on.

tblBudgetcodes: will need to be joined with programs and schools and employees
codename
Fund
Function
Object (regular or overtime)
SubObject (program type)
Org (campus)
budgetyear

*It's important to note that the budget codes are unique and specific for each rate, program type and school. Currently I plan to leave the Org (campus) field blank on tblBudgetCodes to be filled in on the form with the schoolID. I then have tblPrograms looking up the budget codes in 2 fields (one for reg and one for OT). However, that creates 2 budget code tables (tblBudgetCodes and tblBudgetCodes_1) on the relationship page with both linking to tblPrograms and I'm not sure how that will work down the road so there may be a better way to do that and I'm just not thinking of it.

tblJobTitles: will need to be joined with schools, programs and employees
Title
Hours
JobCode

tblSchedules: will need to be joined to schools and programs
meal start time
meal end time
meal session
meal description
delivery method


I'm concerned about my users not assigning things correctly or more to the point, unassigning things correctly and ending up with multiple people being assigned to the same job when they try to move an employee from one job title or program or school to another. I have and can use an employee named "vacant" as a placeholder in an open job I'm just a bit overwhelmed with how to put this all together.

From here I'm at a complete loss how to join all this information correctly. Setting up how many of each job title are contracted for each school and which employee fills that slot. How to assign employees to the various non-contract jobs at each school (keeping in mind an employee can work contract at one and other program at another) while getting the right pay rates and budget codes assigned to each as well. Not to mention how to join up the bell schedules with the appropriate school and program. Also do I need a field in each table for a foreign key from another table if i'm not using the look ups in the tables themselves and instead assigning things in a join table? That may be where the majority of my confusion comes from. For example...on tblPrograms do I need a field for the school, if I'm going to just join them up on another table entirely?

[Dal Jeanis]

First, go over to Roger's Access Blog at http://www.rogersaccesslibrary.com/ and review Roger Carlson's tutorials on database design and application design. An hour or two now will save you loads of headaches later.

Second, try to think in terms of objects or entities.

A Health inspection or a Kitchen Evaluation is probably an entity of its own, not a property of a School. If you want to create an entity like "monitoring event" that covers both of these and the monitoring dates for programs, then that might be a viable design option.

With regard to breaking down the Employee information, you should also think in terms of entities. At design time, remember to differentiate in your mind between aspects of the employee and aspects of his current function. If the employee can have certifications for area 1, 2, 3 and 4, but may be assigned to job duties that only include 1 and 2, then you want to store the employee's certifications separate from his job functions. The jobs are apparently an aspect of the school and program, so your final entities should reflect that.

I have no idea what subs might have to do with an employee. If you are talking about a substitute or assistant being trained, that other person would be related to the job, not to the staff member herself.

Read through a couple of Roger's examples, then apply the method to your own database. Then come back and post your next version.

[ssanfu]

Also, use only letters, numbers and possibly the underscore in object names. (field names, query names, form names, report names, module names)

NO Spaces or special characters -> this is an example of a bad name:
campus #

(a space and the # sign)

[Helystra]

Thanks to both of you. I'll check out Roger's tutorials and do a bit more work on it then I'll be back.

[Helystra]

Quick question...I'm working my way through Roger's tutorials and I realize that I have a small issue in that I want to keep track of administrative employees for schools as well as kitchen staff but only the kitchen staff have employee IDs. From his tutorials I have figured out that I'll need an autonumber surrogate primary key in the employee contact table to join up relationaly with the various other employee information tables I'm going to need to create.

My question is about indexing. After reading his indexing explanations, I'm concerned about using "" (empty string) in the EmpID field since that will create duplicates and I don't want to allow duplicates of actual employeeIDs. But after reading his warnings about NULL values, I'm not sure how that will affect indexing in that field. Is it ok to leave them as Null for indexing or is there a better method?

[ItsMe]

You can index and allow duplicates

Within the table settings under index for that given field's properties. Choose "Yes(Allow Duplicates)". I believe that is what it looks like

[ssanfu]

Having done a lot of research on how to use/pick primary keys, I use an autonumber as the PK in 99% of my tables.
In a table for employees, I can have structure like:

Field Name....................Data Type
EMPID_PK................Autonumber (unique record identifier - not displayed on any form/report)
EmpCompanyID...........Text ( Company assigned ID Num)

The data in the "EmpCompanyID" field is optional.


Here are the sites I found about autonumbers:
1) http://www.utteraccess.com/wiki/index.php/Autonumbers
2) http://www.bluemoosetech.com/microso...ial.php?jid=54
3) http://www.utteraccess.com/forum/Aut...t-t443604.html
4) http://www.fmsinc.com/free/newtips/primarykey.asp
5) http://access.mvps.org/access/general/gen0025.htm

[Helystra]

I see. So allowing duplicates for indexing is ok. But then am I able to disallow adding a new record with an already existing company issued EmpID in forms? I just don't want to end up with multiples of the same empID (either through duplicate entry or input error).

[ssanfu]

Yes, code in the form could verify if the company currently exists.

Or you could try Indexed = Yes (No Duplicates).....
I just tested a text field (FName) set to Indexed = Yes (No Duplicates)
Entered several records with "FName" NULL..... No problems/no errors. Would work the same with EMPID (the data type is Text?).

[Helystra]

Great. And yes, the empid field is text type since the numbers start with zeros. I'll end up going with the code to verify in the form then and I'll probably be back here when it comes time for that code but thanks much for the info.

[ItsMe]

You can index and allow duplicates

Within the table settings under index for that given field's properties. Choose "Yes(Allow Duplicates)". I believe that is what it looks like

Just because you index a column does not mean you have to use that as Primary Key. Index a column within a table when you believe you will "search" for a specific value within the column. An example would be to index a column that contains fields with last names. It is likely you will want to find all of the records with text LIKE "Smith". In comparison, you may not want to index a column that contains fields with first names. Index columns that will help speed up your DB. You will want to index your columns that contain foreign keys.

Having said that, it would not hurt to have a primary key column with an autonumber in addition to a column with Employee ID's. This way every person that has a record within this table can be queried via the primary key, regardless of their title. Indexing the Employee ID column would be advantageous because it will allow for faster searches when a user queries the DB using only the Employee ID. The primary key will be beneficial when you want to query records regardless of the what is in the Employee ID column. You may want to send out a news letter to Everyone.

[Helystra]

Right you are. That's what I learned from the tutorials over on Roger's site that was linked earlier. I have an autonumber "rowID" field that I'll be using for the primary key/foreign key for all the employee related tables. Then i'll index the empID field (which is company assigned) so that searching will be faster.

Great advice all of you. I'll mark this one solved and move on to the next question.

[ItsMe]

I just wanted to clarify... You have a good sized project ahead of you. I am glad you are taking the time to read the links offered.

[Helystra]

Yes, it's a monster database I'm trying to develop. When I'm done, perhaps I'll show you my original attempt which I'm sure will be good for a giggle.

I have just about sorted out the employee tables for the new database. Next will be setting up their relationship and then I can move on to fixing up the other tables. Those tutorials were extremely helpful. I was missing a vital piece in my understanding of how access works and that has flipped a switch in my head for me and turned the light bulb on finally. It's well written in plain english so that even a noob like me can understand.