3 Replies Latest reply on Mar 6, 2017 10:31 AM by BruceHerbach

    Single or Multi Tables

    petewhit

      Hi All,

      I am working on an aircraft maintenance database. A busy area is tracking the components installed on the aircraft. Components are part numbered and serialised. All have unique locations. Some parts are duplicated or more, such as fuel pumps, same part but six installed, depending on the aircraft type.

       

      I consider that the components have to be somewhere, installed, held as a spare or being repaired.

      Some components have maintenance tasks to be performed at certain intervals. Controlled by the calendar, flight hours or flight cycles of the aircraft.

       

      I have a single table with all components including spares. All of them have a location and position which are kept in separate tables. Portals and popovers are used to select locations and positions for the components.

      A single table of components keeps maintenance easier than with several tables for installed, stored or repair, particularly for the maintenance task interval calculations.

       

      Another complication is the major assemblies, such as engines, landing gear and the sub parts on them. Engines move around the fleet of aircraft for various reasons and the unique engine parts have to follow them. They are mostly only changed in an engine workshop. But still need to be recorded.

       

      Each component record is unique with a primary key which is used for relationships. Part numbers and serial numbers can change when a part is modified.

       

      My question is, is it better to have a single table for all components, or several tables?

       

      Thanks for comments!

      Peter

        • 1. Re: Single or Multi Tables
          Johan Hedman

          I suggest you keep all components in one table but have a related field so that you can relate different parts to each other.

          • 2. Re: Single or Multi Tables
            keywords

            I would go further than suggested by Johann. I would keep broad details of each component in one table (componentID, Fuel Pump for aircraft X, part no. xxx-yyy-999), but specific stocked items in a separate Stock table (stockID, fk_componentID, serialNo). The Component table gives you a listing of all the types of components in your database, while the Stock table lists each specific instance of each component in stock. This is the time you would then link to a Deployment table which would in turn link to an Aircraft table, so that you could track exactly which item was deployed where and when.

            • 3. Re: Single or Multi Tables
              BruceHerbach

              You might consider multiple foreign keys in the parts table.  For example you could have a key for current aircraft.  Part ( engine ) moves to another aircraft,  it is easy to update the location foreign key.

               

              Another key would be a parent component.  As your stated,  engines have a number of related parts.  You can have a parentID so that engine widget X is attached to an engine.  Part goes out for maintennce and is replaced by another widget.  Change the foreign keys in both records and the new widget is attached to the engine and the original isn't.

               

              All parts are still in the same table, but can be displayed through table occurrences connected by the correct foreign key.

               

              HTH