12 Replies Latest reply on Mar 25, 2010 7:03 PM by Nerico_1

    Seeking feedback on new design

    Nerico_1

      Title

      Seeking feedback on new design

      Post

      Hi,

      I'm a newbie to FM using FM Pro 11 on Mac OS (Snow Leopard).  I' creating a DB to manage the interview process for our school.

       

      I have 4 main tables and 3 or 4 others that are not much more than advanced value lists.

       

      The first 3 main tables are not related and are pretty self explanatory

       

      Dates - lists the particular dates when we interview

      Interviewers - lists all the potential interviewers

      Applicants - lists all the applicants

       

      The fouth table, Interviews, contains foreign keys for the other three as it lists the particulars of each interview slot.

       

      My question is, is this a workable design?  Is it OK to have 3 foreign keys in one table or do I need to divide this up in some other fashion?

       

      I'm currently doing video training and using a "Missing Manual" book but since they both pretty much just follow up a particular scenario which does not match my needs, my brain is swimming.  I would also appreciate any guidance on good online articles that explain this better by addressing the concept of what should be done or shouldn't be done with fk thoroughly and not as it applies to just one scenario.

       

      TIA

       

       

        • 1. Re: Seeking feedback on new design
          comment_1

          Yes, it is a workable design. However, I am not sure why you need the Dates table as a parent table to Interviews. I could understand using it as a value list, but does it contain any additional information regarding the date that is common to all interviews on that date?

          • 2. Re: Seeking feedback on new design
            Nerico_1

             

            Thanks for the response.

             

            Yes, the Dates table contains additional information that is unique to the date but independent of the actual interviews.  

             

            But I guess my concern is due to another problem I'm having which I posted separately here Problem-displaying-records-in-drop-down.  At first I thought that it might be due to having all the foreign keys in one table.  But then I created a couple of fake two-field tables and I'm still having the drop down problem.

             

            But just in case you can help.  This is what I did.

             

            I entered my initial data for all four tables via imports from excel.  The Interviews table already had assigned interviewers and dates, but not applicants.

             

            I created a layout based on the Dates table with some tabs for different functions.  I placed a portal in one of the tabs containing records from the Interviews table with the following fields...  time, interviewer name (not the fkey, but a calculated field), applicant fkey, and applicant name.   My hope was to be able to use a value list created from the Applicants table to populate the applicant fkey.  But when I try to use the drop down, only the first record from the table pops up.  Does this problem sound familiar to you?  Am I making some simple mistake that I just can't see?

            • 3. Re: Seeking feedback on new design
              comment_1

               


              Nerico wrote:

              Am I making some simple mistake that I just can't see?


               

              Probably - but without seeing the file...

               


              • 4. Re: Seeking feedback on new design
                Nerico_1

                 

                LOL.  I guess by your answer, that there is nothing in the way FM is structured that should prevent me from doing what I described.

                 

                It's just that I used Access in the past and tried to do something similar but I was not able to update the data on certain queries.  It's been a long time, so I can't quite remember the mechanics of it.  I guess I'm still thinking Access queries and not quite grasping FM structure.  I have no idea if that made any sense.

                 

                Anyway.  Is there any information that I could provide that might help you duplicate the issue?  or am I pretty much SOL?

                • 5. Re: Seeking feedback on new design
                  comment_1

                  You could post the file (or - preferably - a reduced version of) somewhere.

                  • 6. Re: Seeking feedback on new design
                    Nerico_1

                    Let me think about it.  I would have to put some fake data into it.

                     

                    Anyway.  Since you are being so helpful, I have another question.  I'm thinking more about my reporting needs right now.  On each particular date we may have a different target number of applicants.  I want to be able to create a report that will list for each date:

                     

                    • Target # of applicants - this is a field in the Dates table
                    • Needed interview slots - this is a calculated filed (2 x target)

                     

                    Then for the interview slots:

                     

                    • Number of available interviews (this would be a count of the records in the interview table that have this date)
                    • Number that have been confirmed (this would look at the same records but count only the ones with confirmed in the status field)

                     

                    Then for the applicanst:

                    • Number of invited applicants (this would be a count of the records in the applicants table that have this date)
                    • Number that have been confirmed (this would look at the same records but count only the ones with confirmed in the status field)

                    So my question is, other than the target field, these are all calculations. How would you store them? as fields on the dates table?  as global fields? How would you make sure that the calculation gets updated when the records get changes?  So far I've only done straight record counts, nothing with additional parameters.

                     

                    • 7. Re: Seeking feedback on new design
                      comment_1

                      There are a number of ways to do this - I'll outline the one that seems (to me) the most straightforward one:

                       

                      Go to the Interviews table and find the records for the date/s you wish to report on. Sort by date and by status. Use a summary field defined as Count of InterviewID to count the records, and use sub-summary parts to show the individual counts.

                       

                      I didn't get the part about applicants "having this date" - IIUC, an applicant doesn't have a date.

                      • 8. Re: Seeking feedback on new design
                        Nerico_1

                         

                         

                        Actually applicants are invited for interviewing on a particular date only and that is handled on another system.  So when we add them to our system is with that particular date already in mind.  But I think it would make the solution a mirror of the interviewers solution you suggested.  

                         

                        Just so I understand correctly, you would put the summary fields in the interview table and not the dates table, correct?

                         

                        And will these recalculate by default, every time I run a new report or do I have to make sure I switch on a particular setting?  

                         

                         

                        BTW.  Thank you so much for your help!

                        • 9. Re: Seeking feedback on new design
                          comment_1

                           


                          Nerico wrote:

                          you would put the summary fields in the interview table and not the dates table, correct?


                           

                           

                          Yes - but the fields you already have in the Dates table need to be placed in the sub-summary by date part.

                           

                          Summaries are produced ad hoc every time the layout is refreshed.


                          • 10. Re: Seeking feedback on new design
                            Nerico_1

                             

                             

                            Got it (I think).  I'm going to go and try it out.

                             

                            But what if I also wanted to use the fields in a layout?  would I have to do something different?

                             

                            I'm thinking of a "dashboard" type tab on my Dates layout where the user would get a snapshot of how the planning for that day is going.

                            • 11. Re: Seeking feedback on new design
                              comment_1

                              You could use calculation fields in the Dates table to count the related records in other tables, e.g.

                               

                              Count ( Interviews::DateFK )

                               

                              will return the number of interviews for the currently viewed date record.

                              • 12. Re: Seeking feedback on new design
                                Nerico_1

                                 

                                Thank you so much.  I'm hoping that by concentrating on a new area (reports)  I'll be able to come back to my other problem later, see it with fresh eyes, and figure out what I did wrong.  Oh well.

                                 

                                Have a great night.  I'm sure I'll see you around when I stumble on my next roadblock :smileyhappy: