4 Replies Latest reply on Jul 15, 2013 8:13 AM by BryanN

    Ideas for Implementation

    BryanN

      Title

      Ideas for Implementation

      Post

           Currently running a db that includes things like work orders, purchase orders, quotes, etc along with the associated customers or vendors.

            

           Looking at implementing a table for test reports (that have a relationship to the work order -> customers.  I've thought about 2 ways of implementing and was curious as to whether one way or another was better:

            

             
      1.           Creating one table that would house fields for all types of reports.  When the user goes to generate a new report, they will get a pop up asking what work order ID and type of report to generate.  At that point, the report layout will only feature fields appropriate to that report type based on the user's input.
      2.      
      3.           Create a table for each type of report and their relevant fields as well as their associated layouts
      4.      
      5.           Creating a separate file for the test reports and linking them to the durrent data file and create tables using either method 1 or 2

           Any suggestions?

        • 1. Re: Ideas for Implementation
          philmodjunk

               There's no real way to answer that question from the information you've provided. Either method or a combination may be the best solution for your users.

               I'd think in terms of test result data, not reports. If your data is organized into efficient tables and relationships, the reporting needs usually become much easier to meet.

               And deciding whether particular data should be all in one table or in several different tables is often as much "art" as it is "science" with experienced developers disagreeing on the best approach to use.

          • 2. Re: Ideas for Implementation
            BryanN

                 What I am really trying to accomplish here is to move test data out of excel spreadsheets (which are form based) and into our db.  Each job can have a one to many relationship with test reports, but the test reports don't really have any relational data per se within them.  For the sake of organization, 1 table per type of testing would be more organized but may be harder to manage with over 15 types of tests that we do.  Just looking for a little advice from anyone who has done something similar.

            • 3. Re: Ideas for Implementation
              philmodjunk

                   And that info does not change my previous advice. Either or both method might be made to work, there's no chiseled in stone answer that fits every possible configuration for what those sets of test data might be like.

                   Here are some questions to answer that can help guide you:

                   How similar are the test results in structure? The greater the similarity in test results from one test to another, the easier it is to put all of that data in a single table. On the other hand, if there are many differences, trying to fit all that data into the same record structure can be come difficult to do.

                   Will you need to produce reports that combine data from multiple tests into a single report? Unless you are only reporting summary values from your data, a combined table of all results offers a lot more flexibility.

                   And don't ignore the possibilty of a hybrid approach where you have a central combined data table for the data that is common to every test that links to related tables customized to the data sets needed for specific test types.

                   And one radical approach that is sometimes worth the effort is to "atomize" your test result data. If all of your data can be represented accurately as a number, a label and a unit of measure ( Max tempurature, 200 degress, celsius or Mass Gain, 2 , KG ...), then a table where you have just those three fields plus a foreign key field to match it to a table of tests can be set up to record your data. That can make for very flexible data storage, but very challenging reporting and analysis of your data.

              • 4. Re: Ideas for Implementation
                BryanN

                     About the only 'common' data will be shown from the related work order or customer info.  The test results are all pretty widely different so I think based on what you have mentioned that separate tables would be the most organized/efficient way of doing it.  Reports only show the data from certain types of tests so it's probably better to separate it out that way if another developer gets their hands on it, they can see the report layout matching the table instead of having to hunt for the data.