1 2 Previous Next 17 Replies Latest reply on Feb 26, 2014 1:12 PM by BryanN

    Best Practices - Create Multiple Tables or Combine All Fields into 1 Table

    BryanN

      Title

      Best Practices - Create Multiple Tables or Combine All Fields into 1 Table

      Post

           So I'm working on a new project in order to make our test reports (used in field testing) as part of our Filemaker database for entry and report printing. We have many different kinds of test reports (at least 10-15) that don't really share data other than the Work Order # and by relationship, the customer. Each report would probably have about 10-30 fields in it for data (all text or numerics with very little, if no calculations). 

           I know there are two schools of thought on this but for performance/development's sake is it better to:

             
      1.           Create a new Table for each Test Report (along with their accompanying data entry/print layouts with only the pertinent data for that specific report available in the table
      2.      
      3.           Create a single Table for all test reports, using a surname for each of the fields in order to differentiate which Test Report they are used on.

           My inclination is to go the single table route as it keeps the relationships cleaner but my concern is that with every report that is created, there would be a considerable amount of fields that are empty - would this affect performance for us? (For reference, running FMS 12, soon to be 13 on a Mac mini Server with GB ethernet using a Data Separation model).

           The goal is that my team would be able to hit (my custom created) button to make a new test report, choose the report type (which will change the layout appropriately) then enter in the data from the data entry layout and be able to print out the accompanying form.

           Thanks for your help in advance!

        • 1. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
          philmodjunk

               There are no hard and fast rules to use to answer your question and there is also a middle ground between your two options--you can set up a unified table with just those fields common to all test reports that links in relationships to "detail tables" set up for each specific test.

               It sounds like you may already have that option set up if you have a table of "work orders" that links to each of your test report tables.

               A key deciding factor can be what kind of reports you need to produce from your data. The more likely you are to need to produce a report that combines data from more than one kind of test, the more likely that you are to benefit from a unified table in order to make that report easier to set up.

               What you want to avoid is the need for scripts that copy large blocks of data from one table to another in order to produce the needed reports. Sometimes that reporting method cannot be avoided, but it can really slow your system performance and can make being sure that your reports are accurate much more complex.

          • 2. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
            BryanN

                 Thanks Phil.  Yeah, I'll use a drop down so they can associate the test report with a job (which relates it to a customer) and any of that data needed will end up on the report.  So it sounds like a unified table is the best bet.  Having a number of empty fields doesn't hurt right?

            • 3. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
              BryanN

                   Also Phil, is it terribly taxing as far as resources go to have the layout dependent on a field value (basically, users will select from a drop down, the type of report and the layout will be scripted to change accordingly)?  Typically users won't be leafing through all reports, just ones that match their searches.  The UI is pretty simple and contains no images, just text and shapes.  Just want to make sure that I'm doing this in the best way possible.  Thanks!

              • 4. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                philmodjunk

                     You can't really redesign a layout on the fly, though there are some modifications that can be engineered if you work at it. It's usually far simpler to set up that drop down with a script that switches to a the appropriate layout.

                • 5. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                  BryanN

                       I'm sorry, I may have mis-spoke.  That's exactly what I plan on doing, that doesn't tax resources too much does it?

                  • 6. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                    philmodjunk

                         It makes for a pretty simple script. Much depends on the reports, your underlying data model and what how each report needs to be formatted.

                    • 7. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                      BryanN

                           That works.  On a similar note, right now all of my 'reports' available for printing or PDF-ing are based on List View layouts using a header, body and footer with the line items (which are based on a child table) being the body.

                            

                           Since for this there will be no child table, is it safe to assume that if we go to print the 'report', that we could choose either the current record or all of the records in the current found set to a PDF file?

                           What I am trying to go for is if one customer says 'hey, could you send me a pdf of all test reports from job xxx' , we could do one print to pdf based off either a search or the current found set instead of having to save each report separately to PDF.  Does that make sense?

                      • 8. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                        philmodjunk

                             A list view layout implies that your report is from a found set of many records. If you select the "current record" option, your PDF will consist of just the data from one record in your found set--a result very different from "Records Being Browsed" with such a report.

                        • 9. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                          BryanN

                               So as I am going, I'm realizing that the table is going to have a lot of fields - I'm up to 290 so far and have about 5 or 6 reports left to go.  Now keep in mind that even though there are a lot of fields, at any given time, around 50 of them will be displayed for either data input or report printing.  I'm also keeping the data fields out of QuickFind to make searches only based on the identifying fields in each test report so that should go quick.  The data is small, usually one number with a few decimal places per field.

                                

                               The way I am doing these is as the user changes a drop down box for the type of test report they need to create, the field triggers a script to change the layout based on the value of the field.

                                

                               When I am done with this, the # of fields in the table could approach 750+, again, with only 50 or so being used or displayed at any given time.  Is there any issue with doing it this way?  (It fits really well with what we are trying to accomplish in terms of ease of data finds, constraining found sets based on relationships and simplicity of my database).

                          • 10. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                            philmodjunk

                                 This sounds like you are adding fields where you should be adding records in a related table. Once you get more than 100 fields in a table, it's time to start doing some careful analysis of your data-model to see if there are design changes that can reduce the number of fields per record.

                                 750+ fields is a truly extreme number of fields to define in a single table.

                            • 11. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                              BryanN

                                   Phil,

                                   Here's the quandary I'm in:  We have about 10 or 11 test report forms we are trying to integrate.  Some of them have data that is common like manufacturer or model or serial# of the equipment but many of them are measuring different types of measures for different types of equipment (breakers vs. switches vs. relays vs. transformers)

                                   Where I am stuck is this data doesn't translate real easy into portals (from a layout perspective) so doing child tables for each type of equipment isn't feasible (in order to have the correct records created in the child table when entering data).  Is there another method by which you can create a child record in the correct table (and enter in data) without using a portal?

                              • 12. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                                philmodjunk
                                     

                                          many of them are measuring different types of measures for different types of equipment (breakers vs. switches vs. relays vs. transformers)...so doing child tables for each type of equipment isn't feasible

                                     I must disagree with that. A portal can be as simple as a single row filtered portal where the borders of the portal are invisible so that this looks like a single field on your layout, but is something that you manage from a related table. It's quite possible to set up additional tables of equipment and types of measurements made on them that can then be included in your report and you have a number of options for how they might be included in your report.

                                     Also, there is no rule that says you have to use a single table for all your reports. This takes us full circle when it comes to your project. If you have a group of reports where the data that you are recording is very similar, it makes sense to use a single table, but the very large number of fields you are describing suggest that this may not be the case. You may need a central table of one record per report, but with sets of related tables that contain detail data in a single related table. Different types of reports would link to different related tables for those details.

                                     Here's an example from a previous job that I had: We had a Non FileMaker Database that, among many other items, tracked the specs for both wine corks and metal screw caps--of which we produce a number of very different types. We had a central table of "SKUs" where we had one record for each wine cork and screw cap that we produced, but we had a table of "cork specs" linked to it for detail specifications unique to natural wine corks and we had other detail tables for each major type of screw cap. The "SKUs" table stored data common to all products that we produced, the detail tables stored data specific to a particular type of bottle closure.

                                • 13. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                                  BryanN

                                       I can see where you are going.  I actually had no idea that I could essentially modify a portal to be transparent and just stuff as many fields as I want in there.  So what you are proposing is create a separate table for each type of report (all child tables) with the parent being a table that would have basic information (like job#, customer, date, etc)?

                                       If so, I have a few questions:

                                       Can I include text labels for fields within a portal's boundaries?

                                       Is there anything that I can't do within the boundaries of a portal that I could do outside of a portal with regard to fields, shapes, labels, etc?

                                        

                                  • 14. Re: Best Practices - Create Multiple Tables or Combine All Fields into 1 Table
                                    philmodjunk

                                         No, I am not suggesting that. I don't know sufficient detail to make that suggestion. I am listing options for you to consider in the light of your view of what data you need to organize into your database. A single row filtered portal may be useful to you.

                                         

                                              Can I include text labels for fields within a portal's boundaries?

                                         Yes. The table view like format you see in a typical portal is just the default placement of fields within the portal. You can make any number of different changes to what is shown in a portal row.

                                         

                                              Is there anything that I can't do within the boundaries of a portal

                                         There are a number of limitations. You can't put a portal inside the row of another portal. Interactive container fields don't work inside a portal row...

                                         But for just displaying data in a report, you aren't likely to hit any major road blocks.

                                    1 2 Previous Next