4 Replies Latest reply on May 31, 2016 2:42 PM by erolst

    Opinions on how to best model this data

    DaveArndt

      I’m creating a solution to track inspections on 3 types of equipment.

      I have a table for the inspections which contains all the necessary inspection fields for the three types of equipment (about 200 fields)

      All three inspections use 90% of the 200 fields.

      The customer requested that all pertinent fields be initially marked as "PASS" .

      From a design standpoint which would be the best way to go about this.  I may have to deal with new fields being added or removed.  Possible additional types of equipment.

      Solution A

      1. Prepopulate all the fields with “PASS”
      2. Run a script which looks at the equipment type and marks NULL the fields which don’t apply.

      Solution B

      1. Prepopulate all fields using a calculation which populates according to the equipment type.

      Would one solution offer better control, performance, or scaleability over the other?  Im at a loss because I can see good/bad to both options.

       

      Thanks in advance for the suggestions.

      Dave

        • 1. Re: Opinions on how to best model this data
          erolst

          Solution C:

           

          Create a table with EquipmentTypes, another one with Inspections (well, what now is represented by your ~200 fields – let's call it sub-inspections), and a third (a join table) that links EquipmentTypes to SubInspections.

           

          Create another table into which you “copy” the join table records that apply to an Inspection, based on the equipment type. Now you have exactly those attributes that you need – no more, no less – in the form of a number of related records.

           

          (Imagine having a template sheet for different inspection types; based on the equipment at hand, you take one, copy it, fill in the name of the equipment and start inspecting and filling in result fields. This is exactly what happens here.)

           

          If you introduce a new inspectionType, add whatever (sub)inspections are required, and create new combinations in the join table. If the definition of an InspectionType changes, modify the join table.

           

          This is how you would set up a database to manage a questionnaire (which an Inspection pretty much is, if you think about it …), under which tag/keyword you should find a number of earlier posts in this forum that will give you more detail and/or sample files.

          1 of 1 people found this helpful
          • 2. Re: Opinions on how to best model this data
            ErikWegweiser

            Hi, Dave:

             

            Although it may require basically starting from scratch, I'd like to suggest the possibility of a different structure. Instead of every Inspection record having so many dedicated fields, perhaps a related table of Master Inspection Items might work better in terms of flexibility and scalability. This Master Inspection Items table would contain a record for every inspection point possible on any Equipment. Furthermore, an Equipment Inspection Point 'join' table would be created, in which each record represents a specific inspection point (from the Master list) applicable to a particular piece of equipment (linking the Equipment table with the Master Inspection Items table). There, you could have a field that specifies what the default entry should be for that particular point for that particular equipment. When an Inspection record is created, a piece of equipment is assigned to it and a scripted routine would add new related records to a child Inspection Point table, according to those set-up for that piece of equipment in the Equipment Inspection Point table. Only applicable inspection points would appear for that inspection and would have the appropriate default entry. This allows for flexibility of adding more equipment, more Inspection Items and changing the Inspection Points individually for each piece of equipment. And without hundreds of 'hard coded' fields.

             

            Does this make sense?

            1 of 1 people found this helpful
            • 3. Re: Opinions on how to best model this data
              DaveArndt

              I think I understand.  For Example I would have 3 tables total ( I know it would be more but just to get past this part)

              MasterItemsList, InspectionPoints, Inspections

              For Example

              An inspection Point in the Master Inspection Item Table would be

              MasterItemList::PK: ML0001

              MasterItemList::Name:"Fuel System Leaks"

               

              The Equipment Inspection Point table would have PK, FK, Inspection type, & Value. In the below example the Inspection Point only applies to the first two inspection types.

              InspectionPoints::PK: "IP00001"

              InspectionPoints::MLFK: "ML0001"

              InspectionPoints::InspectionType: "INS1"

              InspectionPoints::Value: "PASS"

               

              InspectionPoints::PK: "IP00002"

              InspectionPoints::MLFK: "ML0001"

              InspectionPoints::InspectionType: "INS2"

              InspectionPoints::Value: "PASS"

               

              InspectionsChild would have a record for the type E.G. InspectionsChild::Type: INS1

              this would then link Inspections to the InspectionPoints based on the InspectionPoint::InspectionType=InspectionsChild::Type

              Correct?

              If this is the case the InspectionPoints could have over 600 records correct?

              • 4. Re: Opinions on how to best model this data
                erolst

                Do this graphically:

                 

                EquipmentType --< Inspection --< Inspection_InspectionPoint** >-- InspectionPoint --< EquipmentTypeInspectionPoint >-- EquipmentType

                 

                (** I couldn't think of a better name … )

                 

                So every Inspection has an EquipmentType; that lets you find the related set of InspectionPoint records – in the illustration, start at Inspection and go left to find them; imagine a wrap-around screen – and copy them into Inspection_InspectionPoint. That's your questionnaire to fill in in the course of the actual inspection.

                 

                As noted above, the basic idea is that you copy from a template (as defined in EquipmentTypeInspectionPoints) into a working table.

                 

                DaveArndt wrote:

                Correct?

                If this is the case the InspectionPoints could have over 600 records correct?

                At the moment you have 200 fields; assuming that every one represents an InspectionPoint, you'd have 200 records in your master list, which would result in 600 records in EquipmentTypeInspectionPoints for three equipment types.

                 

                But not every inspection type applies to every equipment (which is the crux of the matter, IIRC), so that number would be lower.