8 Replies Latest reply on Oct 16, 2015 11:54 AM by Albud1962

    Relational Database Assistance




      I have attached a copy of a solution that I am trying to develop for a project management function.

      I am assuming this would be a relational database application. The first table is the project table. It contains the project client, name and project number reference assigned by our accounting department. The second table is the task table. It contains individual tasks which link to a project. I am considering it a child of the project table. Each project may have variable tasks assigned to it and the tasks are unique to each project. Lastly, I have a status table. The status table is a child of the task table. The status table is updated weekly or when there is a change in project activities. Each Task can have multiple statuses. the status table has a description field and creation date field. It is intended to track the progress of the tasks. I had the tables joined in a  hierarchal order. I think the problem is the task table is both a parent and child of other tables. When I try to include the project fields on the layout I can't get them to display.  Also my task record keys continue to increment within the task table thus making it difficult  to restart the numbering of tasks for a new project number. I was going to use the project solution that came with Filemaker 14 but its a little too complicated for me to modify for the type of reports I am generating. I just needed a simple report with the project number in the header, the tasks being in the sub summary and then a listing of the status fields.

      Any tips or direction are appreciated.

        • 1. Re: Relational Database Assistance

          There's nothing inherently wrong with the data structure you set up - the relationships between those tables seem fine.


          However, there are a few issues going on:  first, you need to base your layout on a different table.


          * This will help display your Task subsummary lines and multiple Status lines.


          * You need to relate your records together - they don't automatically do that (generally speaking).  When I opened your file they weren't very well connected.  This can be done manually, but that's not real useful and is prone to problems.  It is better to script the creation process or use some of the built in methods (but that requires a portal...).


          *  As for numbering the 'Tasks' - you should be using the PK field as a counter or display to the user.  You can create a 2nd field that will be the 'number'; this field would likely have to be set via script, process - something else that your 'creation' process can handle.  Or you can fake it with a UI merge-object or a portal row number - these numbers wouldn't be permanent, though.  It would depend on whether you want that number to update dynamically (e.g. if a task get's deleted) or if you want it stay static from the time it's created.


          I attached an edited version of your file that shows a layout (based on Statuses) that has the Task as a sub-summary and the Project data listed at the top.  I created some fake records just so there we be more to show; I manually linked everything together by typing numbers into fields - the way that I just suggested you NOT do it. 


          --  Justin

          • 2. Re: Relational Database Assistance

            This is not addressing your questions specifically, but rather an underlying modelling issue which you should consider. As your structure stands it is not easily extensible to more than one project. Consider:


            1.     Although you say that tasks a unique to each project, what you probably mean is that the PERFORMANCE of each task is unique. I don't know what types of projects you are managing, but presumably if you build, say, a single-storey house, and then for a second project, build a double-storey house, many of the tasks that have to be performed are the same. Therefore, you should consider making a Tasks table which contain data on the many tasks that you offer, and a second JOIN table where you link the performance of a given task to a specific project. This model many advantages, not least being that you could record in the Tasks table the time each task is expected to take, and in the Performance table the time it actually took on that particular project—you could go on adding many such examples.


            2.     Along a similar line, you should consider separating Client details from Project details. (What happens when you start a second project for a client you have worked for before?) Here again you can add a JOIN table to link clients to projects.


            Food for thought.

            • 3. Re: Relational Database Assistance

              Thanks Justin! I love the added records too LOL (schmooz with developers haha!)

              • 4. Re: Relational Database Assistance

                Doh!  Can't edit my post anymore.  That third bullet point in my original answer should read:


                "As for numbering the 'Tasks' - you should NOT be using the PK field as a counter or display to the user."


                I'm surprised that I didn't get called on that. 

                • 5. Re: Relational Database Assistance

                  justinc wrote:

                  I'm surprised that I didn't get called on that. 


                  Unlike its Spanish variant, The Best Practices Inquisition can't be everywhere …


                  I think it's OK to let the PK do double-duties, as long as its first duty is to serve as the primary key, and you prevent the user from ever “touching” it.

                  • 6. Re: Relational Database Assistance

                    "Nobody expects..."


                    Yes, it can be not-terrible to use PKs in this manner...BUT this can run into issues if the DB needs to be imported or recovered in some manner, such that the PKs get reset.  Then the number that the human might be remembering could change.  Things would still work, but the consumed data would be different to the user.  There are perhaps ways around this, but it is still something to consider.


                    And with the original issue around wanting to restart numbering of Tasks, then the PK is definitely not the thing to use for this purpose.

                    • 7. Re: Relational Database Assistance

                      justinc wrote:


                      "Nobody expects..."    Yes, it can be not-terrible to use PKs in this manner...


                      Well, I was just saying that you can do it – as long as you don't need to do anything to the PK to make it play its other role(s).


                      Once that becomes necessary, create a dedicated field.

                      • 8. Re: Relational Database Assistance

                        Thanks for all the replies. I was able to use a summary running count to enumerate the tasks for each summary sorted by project num. I was able to get the output report I wanted albeit its a little clunky. I attached a clone of the final db.