5 Replies Latest reply on Dec 25, 2008 1:17 PM by jd2775

    Need tips on designing Database



      Need tips on designing Database


      Hi there, 


      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)

      -Insects (Name)

      -Crops (Name) 


      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!



        • 1. Re: Need tips on designing Database
          Jens Teich
             It is a bit difficult to handle such a big problem in a discussion forum like this.

          IMHO the most important guideline of FileMaker database design is the separation of the relationship graph in subgraphs according to Kevin Frank (http://kevinfrank.com/anchor-buoy.html). If you try to solve everything with 'the one and only true' graph you end up in chaos.

          Have you enough books to read? I like 'Using FM 9' very much and the official FM training material 'Foundation Series' is valuable. The later shows a very good approach in db design.


          • 2. Re: Need tips on designing Database

            Sorry you are right, my questions were too broad.   Yea, I have the FileMakerPro9 Missing Manual, they basically talk about designing Inventory databases in it (in the relationships sections), I am having a hard time relating that to what I am trying to do. 


            Ok, I have a much more specific question :)


            Is it possible to select multiple items from a dropdown list?  Once I get this designed I would prefer drop down menus to check boxes, because of the sheer number of items in some of them.  Also, when I want to view as a table, I don't want those fields showing as check boxes in the table itself which they seem to do.


            Thanks again 

            • 3. Re: Need tips on designing Database
              Jens Teich
                 In FileMaker there unfortunately is no way to convince drop downs to accept multiple selections[*]. This is only possible with check box sets.


              [*] Here I was wrong see post from comment

              • 4. Re: Need tips on designing Database

                > Is it possible to select multiple items from a dropdown list?  


                On a Mac, hold the Command key while selecting/deselecting. I suppose there is an equivalent modifier key for Windows users.



                However, judging from your first post, you'd be better off using a join table for multiple selections. You could be selecting from a portal, and have a script create a joining record for each clicked item.

                • 5. Re: Need tips on designing Database

                  Thanks for the tip, actually using a portal might be good



                  Hmmmm you have also given me an idea on how to relate certain fields from certain tables when I design the Report and Details Layout.  Portals might be the way to go.  I will see what I can come up with