Looking for guidance in designing a component maintenance tracking DB: OOoBase

[Dngrsone]

I have discussions here, here and here.

What I want to do is build a tracking database for an item I repair at work: an aircraft navigational receiver. There are currently some 19 service bulletins issued by the manufacturer for this unit which address specific issues which have been discovered to be problems or for adding new capability to the units operation.

A few of these SBs modify the part number of the unit, so I may want to add some constraints later on for data validation purposes.

Nearly all the SBs are recommended but not mandatory. However, my employer has recently decided that one particular SB must be installed, and they are giving me a hard time because I can't tell them how many of the 500 or so units we have in our system need to be modified. Can you imagine how time- and labor-consuming it is to look into the belly of every jet of a regional airline to see if its NAV receivers have a little teeny square on its data plate marked out?

Anyway, in an effort to prevent this kind of informational gap from occurring again, I want to track the units by part number and serial number (even though they are all the same basic unit, I can't guarantee that there aren't two different part-number models out there with the same serial number), list the service bulletins installed on each, along with part numbers and revision numbers of each of the six major sub-assemblies. While I am at it, I can also track the Work Orders the units come in on for repair, what I found wrong with them, and what I did to repair them as well as other repairs and maintenance I performed before reissuing them out to the fleet.

That way, when the boss asks me for high-failure items, or how often a unit can go between alignments, I will be able to issue somewhat accurate statistics.

Since I use Ubuntu almost exclusively, then my choice of DB app defaults to Open Office.org BASE, which runs on HSQL, apparently.

I have a script which I wrote to create the tables I think I need, and I am currently thinking about the forms I need to build, but there are still a few issues I have to work out-- what's the best way to track the configuration of each unit (identified by a composite key of unit P/N and S/N)?

I've thought about using a separate table with the service bulletins (as boolean entities) and indexed with a unique integer primary, but then how would I ensure each index number associates with a unique set of boolean values?

I thought the most efficient way would be to have a binary-coded hexdecimal number to identify which services bulleting were applied to a unit, but I have no idea how to encode and decode the information through a form (the user would check the appropriate checkboxes and the coded number would be automagically applied to the database and vice-versa).

I have attached a rough UML I drew up in DIA Diagram Editor.

I have also attached the SQL script I used to create the latest iteration of my database, but I have done something wrong, I think-- the linking tables aren't updating for me and I have no idea what I need to do to get them to work, nor if there shouldn't be some different way of tracking the data.

[Dngrsone]

So, no advice, then? Not even a "that's interesting"?