3 Replies Latest reply on Oct 8, 2012 7:16 AM by pad-man

    Newbie questions. Starting DB from Scratch

    pad-man

      Title

      Newbie questions. Starting DB from Scratch

      Post

           I am new to FMP and databases. I have taken the time to watch the Chris Ipolite  tutroials on Lynda.com I am in the ERD stage and have a few questions. I have six entitites: Client, Location, Unit, Personel, Personel Training Records, & Medical Direction. The Client to Locations, is a one to many relationship, each client may have one or many locations. At each location there is at least one unit and there may be many, so the relationship of Location to Unit is One to Many. Each Client has Personel. The personel may be at one or many of the locations so the relationship of personel to locations is one to many. The personal traing records, now that I am writing this, is a one to one relationship as each person has only one training record so I gues the traing record fields can go under the Personel table and reduce my tables by one entity leaving Client, Location, Unit, Personel & Medical Direction.

           The relationship of Medical  director to client is a one to one relationship, which would mean based on the tutorials, that I dshlould make the Medical Direction a Field on the client Table but I want to compile the basic contact info for the Medical Director so I think this should be a seperate table.

           I have included my diagram to make sure I have the correct relationships and keys. Without complicating my endeavor for now I will like to be able to have the corresponding client documents archives along with daily weekly monthly and quarterly unit check forms in this database as well but I know I need to come up with the basic design first. I am not using a starter design FYI, I am starting from scratch.

           Any help is greatly appreciated.

           Thank you.

      photo.JPG

        • 1. Re: Newbie questions. Starting DB from Scratch
          davidanders


          http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
          Home > Designing and creating databases > Creating a database > About planning a database
               A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.

               Follow these general steps to plan a database:
          <SNIP>

          http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
          Relational Database Design 101 (part 2 of 3)

          http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
          Relational Database Design 101 (part 3 of 3)




          MY STANDARD LIST OF FILEMAKER LINKS

          The White Paper for FMP Novices is useful  - 
                                   http://foundationdbs.com/downloads.html
                                   
                                   Filemaker Free - Listing of free resources  - 
                                   http://filemakerfree.com/


          Free unlocked templates
                                   By Richard Carlton Consulting
                                   Starting Point -
                                   http://fmstartingpoint.com/
                                   Donations -
                                   http://filemakerdonations.com/
                                   Recruiter -
                                   http://rccrecruiter.com/alt-index.html/
                                   Data4Life -
                                   http://data4life.net/

          RCC Blog  - 
                                   http://filemakerbits.wordpress.com/
                                   
                                   By yzysoft.com  Contacts | Products | Invoices | Letters
                                   Sample Database -
                                   http://yzysoft.com/printouts/yzysoft_SampleDatabase.html

                                   
                                   A free calendar is available and can be integrated into your database
                                   Seedcode Calendar Free -
                                   http://seedcode.com/cp-app/prod/calendarfree10/
                                   
                                   Filemaker's  Online Help Pages
                                   FMP 12  http://www.filemaker.com/12help/index.html
                                   FMP 11  http://www.filemaker.com/11help/index.html
                                   FMP 9    http://www.filemaker.com/9help/index.html


          FMP business database demos - some are fully useable
                                   The Excelisys Business Tracker V3.0  - 
                                   http://excelisys.com/exbiztracker3.php/
                                   
                                   Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet   Contacts | Meetings | ToDo List | Links | Notes/Files
                                   Google "Campus Productivity Kit"    https://www.google.com/search?q="Campus+Productivity+Kit"

          WIN -  http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
                                   Machttp://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/

                                   You can rename the database from a .usr file to a .fp7 file and open in FMP 7 thru 11

          Lynda dot com has Filemaker Videos (portions are free)
                                   http://lynda.com/FileMaker-training-tutorials/116-0.html
                                   
                                   YouTube - Filemaker Videos

          FMTutorials Channel
                                   http://www.youtube.com/user/FmTutorials/
                                   
                                   UK FileMaker Channel
                                   http://youtube.com/user/UKFileMaker/
                                   
                                   FileMaker, Inc. Channel
                                   http://youtube.com/user/filemakerinc/
                                   
                                   Lynda.com Channel - Filemaker videos
                                   http://youtube.com/user/lyndapodcast/videos?query=filemaker
                                   
                                   Dwayn Wright's Channel
                                   http://youtube.com/user/FileMakerThoughts/


                                   Soliant Consulting Channel
          https://youtube.com/user/SoliantConsultingTV/

          Filemaker Academy Channel
                                   https://youtube.com/user/filemakeracademy/
                                   
                                   Skeleton Key Channel
                                   http://youtube.com/user/SkeletonKeySTL/


                                   Accelerate Computer Training Channel
          http://youtube.com/user/AccelerateTraining/
                                   
                                   Filemaker News

          Scoop.it is an information curation site - free option available.
                                   Filemaker Info  -  http://scoop.it/t/filemaker-info
                                   Filemaker News  - http://www.scoop.it/t/fm-news

          Filemaker Wikipedia  - 
                                   http://en.wikipedia.org/wiki/FileMaker
                                   
                                   Filemaker - Version By Version  - 
                                   http://tokerud.com/filemaker/


          Search specific Filemaker sites with this Custom Google Search
          http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8
                                    

                

          • 2. Re: Newbie questions. Starting DB from Scratch
            Sorbsbuster

                 "Each Client has Personel. The personel may be at one or many of the locations so the relationship of personel to locations is one to many"

                 If each personnel will belong to one client, and each personnel is only at one location, that's fine.  But if a personnel can cover several locations you will need a join table between Personnel and Locations.

                  

                 "The personal traing records, now that I am writing this, is a one to one relationship as each person has only one training record" - I doubt that is a flexible long-term view.  Surely personnel will accummulate a training record for basic this, advanced that, certified the other.  At any stage you will want to see all the personnel with basic training in this, and due for renewal, etc.  I would create a Training Record Table.

                 "the training record fields can go under the Personnel table and reduce my tables by one entity" - I think saving one table is a false economy.  I would say always create one table too many that one too few.

                 "The relationship of Medical  director to client is a one to one relationship, which would mean based on the tutorials, that I should make the Medical Direction a Field on the client Table but I want to compile the basic contact info for the Medical Director so I think this should be a seperate table."  If one client has only one medical director (at a time) then that is correct.

                 "I will like to be able to have the corresponding client documents archived" - my view is never create an archive file unless there is some really big driving reason for it.  You will create the extra work of the archiving process, maintaining two compatible tables, run the risk of mis-match between records that are 'live' and 'archived', and at some point I guarantee you will want to look at data that doesn't care whether the records are 'old' or not - probably even wanting to see them on one report.  There probably are examples of where an archive table is worth it, but I have never used one.  Simply have a field 'ArchivedYesNo', or 'ActiveYesNo' and tag it as appropriate.  Portals, searches, relationships etc can all be made to ignore the 'Archived' records so users are not bothered with them day-to-day, but can access them immediately when they do want to see them.

            • 3. Re: Newbie questions. Starting DB from Scratch
              pad-man

                   David- TY for all of the links and information. There is a tremendous amount of inforamtion there. I wish I had all the time to go through it all! I have booked marked all of it and will slowly start to digest it all. As I said I wish I had the time to read it all and then start on my DB. The reality is I will need to build it  and learn as I go, which for me has always worked better via trial and error, than reading an inordinate amount of information and then trying to remeber what was said and how to apply it.

                   With that said, I wanted to get input on my daabase design. From what I read this is a critical part of the DB design which is why I posted my diagram here, to get the feedback on this initial stage.

                   So Sorb

                   "If each personnel will belong to one client, and each personnel is only at one location, that's fine.  But if a personnel can cover several locations you will need a join table between Personnel and Locations." I will make them a join table. What do I cal this table?

                    

                   , to reply to your comments about the Training Records and Personell...I would agree with your thinking that there usually are a list of trainings andf certifications to track, hence the Join Table idea.In my instance I only have to track one certification, CPR. For each person, I need to have the following data concering their training, Date of Class, Name of Instructor, Name of training facility, date of expiration, Course affiliation ( AHA or NSC). Basic Personell data to be kept is: Name, address, emil, cell, department they work for and location.

                   So it is possible to keep all of this in one table or do you still think I should have a Training Table and use that as a join table between Personell and what? I will want to generate a report and an email from this so I can notify a supervisor and the person with an email of their coming expiration date. For example I will want to generate a report that lists all expiring personell in the next 90 days and send them email and calendar notifications. With this being the case, it looks like I have a one to one relationship f personnel and training record. Each personnel has only one record for me to maintain.

                   Both Dave and Sorb, thank you for taking the time to read and reply to my post. I appreciate the effort.

                    

                   Adam