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

**Dngrsone** · Dngrsone, July 2nd, 2010 12:27 AM

I have discussions here and here.

What I want to do is build a tracking database for an item I repair at work. 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).

Any suggestions?

**jmurrayhead** · jmurrayhead, July 2nd, 2010 08:09 AM

First off, can you explain what a configuration is (possibly providing sample data) and what exactly you're wanting to track?

**Dngrsone** · Dngrsone, July 2nd, 2010 09:10 AM

Okay, what I am working on is 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.

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.

**jmurrayhead** · jmurrayhead, July 2nd, 2010 09:20 AM

I'm unable to view the DIA Diagram Editor file, but I did check out the spreadsheet you posted on the other forum. What is A1 P/N, A1 Rev, A2 P/N, A2 Rev, etc?

**Wolffy** · Wolffy, July 2nd, 2010 09:46 AM

I think you will find working with composite keys to be painful -- I try to avoid them at all costs. Without looking over your current script, I would suggest the following:

First, consider a table for Parts that basically describes each part:

Code:

Parts
PartID  int PRIMARY KEY INCREMENT(1,1)
PartNo  varchar(15) -- Or whatever works in the real world
Desc     varchar(255) -- Again, adjust as necessary
-- Whatever else you need.

Now consider that a Serial Number represents a instance of a particular Part, and this should be in a separate table -- one record for each physical part.

Code:

Fred -- OK, I couldn't think of a good name for this table this early
FredID int PRIMARY KEY INCREMENT(1,1)
PartID int FOREIGN KEY PARTS(PartID)
SerialNo varchar(32) -- What ever makes sense
-- Whatever else you need

Note that this isn't any real SQL syntax, but for illustrative purposes

Basically, there are two concepts here.
First, don't mix two (or more) concepts into a single table. The Parts table describes what a particular part is: 1-inch U-Bolt, 10mm Spanner, 440-Ohm resistor, etc. The Fred table (I know, bad name) represents a physical instance of a part, such as that 10mm Spanner over there.

Second, separate the business key from the virtual key. In this case, the Part Number is a business key whilst the auto increment is the virtual key. The VK is used internally by SQL to form the relationships between the tables whilst the BK is what we humans use. This way, if the BK needs to change (and it does happen), you only need update one field in one record in one table rather than refactor your entire database.

**don94403** · don94403, July 2nd, 2010 04:39 PM

To reinforce what Wolffy said, the best way to design a relational database is to really understand that it is a model of something in the real world. To design it, you need to be very explicit about defining each entity in the real world that you need to represent in the database. Instead of just collecting a bunch of fields into a table and calling it by some vague name, you have to first identify all the entities, like:
TopAssemblies
SubAssemblies
Parts
WorkOrders
ServiceBulletins
etc.

Every entity will be represented by a table, whose fields are the attributes of that entity.

This is the kind of analysis that will lead to a workable schema.

**Dngrsone** · Dngrsone, July 2nd, 2010 10:08 PM

Okay, the major subassemblies are referenced as A1, A2, etc. These are removable circuit cards and such.

Now. the reason I was using composite primary keys is because I don't know if there are two units out there with the same serial number but different part numbers. This shouldn't happen, considering that all three part numbers are the same machine with different configurations, so I suppose I could dispense with those and deal with that problem when I get to it.

Do I not have my tables separated correctly in my script? I have separate tables for the revisions, modes of failure, maintenance work orders, adjustments made, etc.

**don94403** · don94403, July 2nd, 2010 11:53 PM

I agree wholeheartedly with Wolffy on this. The role of the primary key is strictly to uniquely identify each row of a table so that relational joins are possible. Sometimes there is a natural primary identifier that can serve that purpose, but it is often FAR simpler to create a unique identifier such as an auto-increment field that is used in joins, but has no external significance. Just treat your part numbers and serial numbers as descriptive data.

**Dngrsone** · Dngrsone, July 3rd, 2010 12:01 AM

Okay, I can do that.

I can constrain the serial number field unique without it being a key, correct?

**don94403** · don94403, July 3rd, 2010 12:57 AM

Originally Posted by Dngrsone

Okay, I can do that.

I can constrain the serial number field unique without it being a key, correct?

You can specify a unique index for a field, yes. At least in MySQL and, I think, probably in nearly all databases. Specifying a unique index will serve as the constraint, which is really the same mechanism that's used for a primary key.

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

LinkBack

Thread Tools

Display

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

Similar Threads

Parameter guidance

Site Maintenance for Thursday, June 11, 2009

Email Tracking Code

Know any bug tracking software?

PDF Component

Bookmarks

Bookmarks

Posting Permissions