Need tips on designing Database
I have been put in charge of designing a Pesticide Application Database at the research greenhouse I work at. Currently we have one designed in Access by a former manager, and my current manager asked me to look at reformatting it in Filemaker to make it less complicated. Thanks to David from this board (ULearnIt), I have got a Insect Scouting and Reference database in place, but I need help designing this one...
Ok, the basics...
This database will have the following tables and records frontloaded (most information going in all at the beginning, with new records added occasionally but not often)
-Applicator (Name First and Last, Phone Number, QAC License Number)
-Chemicals (Name, Active Ingredient, Warning Level, Scientific Name, EPA Registration Number)
-Chemical Compound- (Just the name of the chemical)
-Greenhouse (ID number e.g, 14A)
Then there will be a "working table" I guess I would call it, where data would be entered at the time of each application, usually 2x a week, depending on severity:
-Application Information Table (Crops(could be multiple for one room), Date, Time, Re-Entry Date, Re-Entry Time, Applicator, Control Agent, Greenhouse Number)
FROM this information a Details report would be printed out and posted on each Greenhouse with the following information:
Greenhouse Number, Applicator First Name, Applicator Last Name, Applicator QAC Number, Phone Number, Application Date, Application Time, Re-Entry Date, Re-Entry Time, Control Agent, EPA Reg# (This would be a form with those pieces of information spread around it for ease of reading, basically NOT in table form)
Also, a 30 Day Report would be generated at that time to include all information about < or = to 30 days worth of applications, in a table format. This is basic for regulatory purposes
Ok, so, any tips on how to design this? The Scouting Database was fairly easy compared to this as it was 2 tables with a couple "sub" tables within.
Basically, I am trying to figure out how to link the tables, so when application information is entered it links up with the appropriate information from other tables to make these reports, and keep everything organized.
Any tips would be much appreciated!