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!