3 Replies Latest reply on Feb 19, 2012 5:08 PM by MacCrollman

    New to Filemaker: need advice in designing a database

    R.Barre

      Title

      New to Filemaker: need advice in designing a database

      Post

      I just downloaded the Filemaker trial and am hoping you can save me a few decades of stumbling around like a ninny.

      I would like to create a kind of Project Management database.  Let's say we have 100 steps in our Project.  I'd like to create something like a folder or bin for each Step into which I can put a variety of items: notes, questions, research photos, diagrams, audio and video files, text documents, etc.  So if I'm having a meeting with staff on Step 4, I can just open up the Step 4 bin and find everything I need.  

      But here's where it gets complicated.

      In each Step, I'd like to assign some of these notes, questions, files, etc. to particular departments.  And since many of these Steps are performed at different locations, I'd like to assign each step to a particular location.  So if I'm having a meeting with the Sales Department, I can view all my information by department, and it will show the notes in Steps 3, 18, 43 and 89 that are assigned to Sales.

      And if I'm meeting with everyone from the Chicago office, I can view the information by location and see just those Steps (2, 45, 68 and 94) that take place in Chicago.

      See what I mean?  

      I'm just not sure how to set that up.  Is there a template out there sort of like what I'm trying to do?  Or some advice as to how I could get started on something like this?  As I said, I have no experience in creating databases, so be gentle.

      Thanks in advance for any advice you can offer me.

        • 1. Re: New to Filemaker: need advice in designing a database
          LaRetta_1

          You first need the correct structure.   Look at each group of data and begin drawing on paper, asking these questions:

          Can a Project have more than one Step?  Yes.  Then Steps must be related table for multiple records.  Called 1:n one-to-many relationship.

          Can one Step ever be in more than one Location for that specific Project?  If not then Location should be a field in Steps.

          Can one Step have multiple Documents/attachments?  Yes.  So Documents must be 1:n to Steps.

          Can Documents ever be linked to more than one Project?  More than one Department?  More than one Step?

          Tables/fields might look like this:

          PROJECTS
          ProjectID (unique, FM-generated, auto-enter serial number)
          ProjectName
          ... fields for other details specific to a project

          DEPARTMENTS
          DepartmentID (unique, FM-generated, auto-enter serial number)
          DeptName (text )

          LOCATIONS
          LocationID (unique, FM-generated, auto-enter serial number)
          City

          STEPS
          StepID (unique, FM-generated, auto-enter serial number)
          ProjectID (number)
          StepNo (number) ... this is unique order that you want the steps performed.  It is manually entered by management to display sort order etc.
          StepName (text ) ??? whatever you want to call your steps
          LocationID
          ... fields for other details specific to a Step

          ATTACHMENTS
          AttachmentID (unique, FM-generated, auto-enter serial number)
          Attachment ( container ) ... store as reference

          NOTES *
          NoteID (unique, FM-generated, auto-enter serial number)
          CreateDate (date) ... I always prefer timestamps
          StaffID (text ) created by
          ProjectID
          StepsID
          AttachmentsID
             * purpose of multiple IDs is so that notes can be related to any table directly but User can search only one Notes table and find notes pertaining to anything written.  It also speeds your solution because all of these notes (being in a related table) aren't loaded when your main tables load.

          STAFF
          StaffID (unique, FM-generated, auto-enter serial number)
          FirstName
          LastName
          DepartmentID (are staff assigned to a Department?)  ... so if you want to find all staff who might have worked with documents assigned to Sales, you could find them.  There are general reasons as well, just for displaying your employee hierarchy. I also wonder about you assigning a document Attachment to a Department.  Might that be a lookup from the Staff table, pulling Department from staff who created the Attachment record?  These are just considerations while designing.

          After you have created your tables then you can begin linking them as you think they should go; you can change them if incorrect.  I can only assume:

          Projects --< Steps --< Attachments

          ... how to link the rest will be determined by your design on paper where you've asked yourself the same kinds of questions I asked you above.  You may also need one or two join tables.  Here is a link to a pdf called "Thinking About Solution Design."  It was written by Ray Cologon, Ph.D. (aka CobaltSky), one of the world's top Developers.  It was written in 2002 but still holds true today. 

          http://www.nightwing.com.au/FileMaker/resources/ThinkingDesign.pdf

          If you find yourself creating multiple 'like' fields, it means you should split them off to a 'many' relationship as another table with records.  Once you get this created, you can easily perform a find 'through your relationships' to display and retrieve the data in any way you wish.  Proper foundation is the key.  Keep this thought in mind, "When in doubt, split it out."

          • 2. Re: New to Filemaker: need advice in designing a database
            R.Barre

            Thank you SO much for the thorough and thoughtful reply.  I'm jumping right into it with gratitude.

            • 3. Re: New to Filemaker: need advice in designing a database
              MacCrollman

              this really is a great answer!

               

              it helps me too.