Recommendation for Table structure 1

[FoxProProgrammer]

Hi,

I created a database to track the repair of equipment. The database works fine. The user now wants to add functionality to track the repair of subassemblies that were pulled from the equipment.

Repair Level 1: A technician pulls a defective circuit board from the equipment and installs a good circuit board in its place.

Repair Level 2: A technician repairs the circuit board that was pulled from the equipment. This requires replacing specific components on the circuit board, such as integrated circuits, resistors, capacitors, etc.

Each repair requires a Work Order (Job).

I created a database about a year ago that allows the user to look up parts used on the circuit boards. This was well before they wanted to track the repair of the assemblies or subassemblies. Here is the structure of each database:

Repair Level 1:

tbl_jobs
jobID, Autonumber (primary key)
jobNum, Text
date_completed, Date
Item_Repaired, Integer
Item_SN, Integer
comments, Text
No_Fault_Found, Yes/No
CustomerID, Integer
EndItem, Integer

tbl_subassembly
PartID, Autonumber (primary key)
PartNum, Text
Description, Text
Repairable, Yes/No

tbl_parts_replaced
JobID, Integer (Foreign key to tbl_jobs)
PartID, Integer (Foreign key to tbl_subassembly)
PartSN, Integer


Repair Level 2:

tbl_lower_assembly
assemblyID, Autonumber (Primary key)
part_number Text
description, Text

tbl_part_numbers
PartID, Autonumber (primary key)
PartNumber, Text
TypeID, Integer (foreign key to tbl_type)
Description

tbl_subassembly_components
PartID, integer (Foreign key to tbl_part_numbers)
subassemblyID (Integer) (Foreign key to tbl_subassembly)
circuitID, Text
notes, Text

tbl_type
TypeID, Autonumber (Primary key)
Type, Text

I already modified the data in tbl_subassembly to merge tbl_lower_assembly and tbl_subassembly together. The question now is whether I should use tbl_jobs to collect data on both the assembly and subassembly repair. The two choices seem to be:

1. Continue using tbl_jobs only for the upper level assembly repair. Create a new table to store repair jobs for the subassemblies.

2. Add the subassembly jobs to tbl_jobs.

While option 2 would allow tracking of all jobs in a single table, it has some drawbacks. For example, some of the fields in tbl_jobs aren't applicable to subassembly repair. The database needs to store the level of repair. If I use tbl_jobs for both levels of repair, it seeems as if I would have to add a field to identify whether the repair level is 1 or 2.

Does anyone have a recommendation for how to structure what is basically the merger of these two databases?

Thanks very much for your time!


dz

 

[Darrylles]

Hi Fox,

Are you aware that you can relate a table to itself?

E.g.
tblRepair
jobID
blah
blah
blah
repair_level (=1,2 or 3 (top-level, assembly, sub-assembly)
job_id_fk (if repair_level = 1, then this will be empty, otherwise it will contain the job_id of the parent record).

Sounds to me like regardless of the repair level - each repair stores virtually the same type of information.

Does this sound interesting?

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[FoxProProgrammer]

Hi Darrylle,

Anything that helps sounds interesting! )

I didn't know that you could relate a table to itslef, but the purpose of it hasn't soaked in yet. I'll have to think about that more and get back to you. If it wouldn't be too much trouble, could you please elaborate on your example?

Some of the information is the same no matter what the repair level is. For example, all repairs have a work order number, date completed, comments, and part number of the item repaired. However, some information is only applicable to assembly repairs (serial number of circuit board replaced, next higher assembly), while some data is only applicable to subassembly repair (circuit ID of the component on the board). While you might think that the next higher assembly for circuit boards is known, this isn't the case because some circuit boards are common to multiple end items. Once they are pulled, there's no traceability to what they were pulled from (nor does the user care).

Foremost, I want to make sure that the tables are properly normalized and that I can display the information to the user and create reports. For repair of the main assembly, the user clicks a combo box and a list of parts applicable to that unit are displayed in a list box. For repair of subassemblies, the user clicks on a combo box and the components on that circuit board are displayed in a list box. I have all that working, but am stuck on whether to add all the jobs to tbl_jobs, or create a new table to store the jobs for the circuit board repairs. It seems like there will be a lot of null fields if I combine the jobs for the upper level and subassembly repairs. Is this a problem?

Thanks again for your suggestion.

dz

 

[rac2]

Yes, those null values in certain kinds of rows in the Jobs table dont feel right. Neither does the introduction of a TypeOfJob column. It seems like the info in those optional columns dont really tell us about the job. If they are not attributes of a Job then maybe they need to be located somewhere else.

Maybe you could define separate tables describing assemblies with columns for serial number of circuit board replaced, next higher assembly; and for cicuit boards. That way there wont be any null fields.

Then add a couple more tables for RepairedAssembly and RepairedCircuitBoard. Each of these tables would have a foreign key column indicating the jobID. There would not be any part numbers in the Jobs table.

This way a join of Jobs and RepairedAssembly would give a complete description of the job in which an assembly was repaired; a join of Jobs and RepairedCircuitBoard likewise of those repairs.

A UNION of those two queries which only had columns that matched up from the separate tables would give the complete list of Jobs with the ID or description of either the assembly or the circuit board.

I am thinking there would also be tables for Assemblies and CircuitBoards. The ID fields in those tables would be used in RepairedAssembly and RepairedCircuitBoard respectively. No other info about the equipment would appear in the Repaired... tables. If you need to show a description then you get it from a join.

SELECT j.JobID, j.JobNum, j.DateCompleted,
       a.PartNum AS "Part Identifier", a.Description
FROM Jobs j
JOIN RepairedAssembly ra ON ra.job_id = j.JobID
JOIN subassembly a ON a.PartNum = ra.part_num

UNION 

SELECT j.JobID, j.JobNum, j.DateCompleted,
       b.part_number, b.Description
FROM Jobs j
JOIN RepairedCircuitBoard rb ON rb.job_id = j.JobID
JOIN lower_assembly b ON b.assemblyID = rb.assembly_id

The UNION query shows the data which can be matched up for the two different kinds of repairs.

Separate queries would be used to show data which is specific to the type of repair.

So the columns in RepairedAssembly would just be foreign keys, job_id and assembly_id; in RepairedCircuitBoard they would be job_id and part_num. These tables are in a sense the interface between the job and the type of equipment being repaired. They represent the reality that we are no longer repairing just assemblies; sometimes we are repairing assemblies, sometimes circuit boards. It is a distinction that must be represented in the database design.

 

[FoxProProgrammer]

Hi rac2!

Thanks for your detailed reply. I'll give your post much more thought tomorrow, but I wanted to reply to some of your comments while they fresh on my mind.

Then add a couple more tables for RepairedAssembly and RepairedCircuitBoard. Each of these tables would have a foreign key column indicating the jobID. There would not be any part numbers in the Jobs table.

Actually, the portion of the repair database that I already created is structured like you suggested. The table, tbl_parts_replaced, has two foreign keys (partID and jobID). Links to the circuit boards that were replaced are stored in this table, along with the serial number of each board. I agree with you that I could (should) create a similar table for circuit board repair - though it won't have the serial number field because it doesn't apply to components on boards.

Only two fields are stopping me from including the repair records for the higher assembly and circuit boards in the jobs table. They are end_item and customer. These two fields are only applicable to repair of the higher assembly. When I first designed this database, they fit there. Maybe now I need to move those two fields to another table, which is related it to tbl_jobs through the jobID?

I still need to specify whether repair records in tbl_jobs are for higher assemblies or circuit boards. If this information doesn't belong in tbl_jobs, I could create another table (e.g. tbl_level) that contains the jobID (foreign key to tbl_jobs) and a field that indicates the level of repair. Is this better than including this data in tbl_jobs?

If I'm heading the right way with this, I need to add three tables:

tbl_level
jobID (foreign key to tbl_jobs)
level (indicates higher level or circuit board)

tbl_circuit_board_repair
jobID (foreign key to tbl_jobs)
PartID (foreign key to tbl_components)

tbl_higher_assembly
jobID (foreign key to tbl_jobs)
end_item
customer

Is this heading the right direction?

Thanks!

dz

 

[rac2]

The really essential thing about the Jobs table I suggest is that it does not have any column referring to the thing that is repaired. No thing_id and no thing_type.

You would determine the type of thing repaired by the table in which the repair is stored. From there you would find the JobID which authorized and tracked the completion of the repair.

Likewise you would find the things repaired in the job by looking in the repair tables. First look in the repair table for the complex things (assemblies?), second look in the repair table for the simple things (circuit boards?). If you need to report the whole set of things repaired, use UNION to combine the two queries.

If you need to report extensive details about one of the things which was repaired query the table which describes that kind of thing.

If you also need to report info about the Job together with the item repaired, then join three tables as I show in my code above, Jobs, RepairedThing, ThingDetails. Or using the names in my code above,

SELECT j.*, rb.*, b.*
FROM Jobs j
JOIN RepairedCircuitBoard rb ON rb.job_id = j.JobID
JOIN lower_assembly b ON b.assemblyID = rb.assembly_id

 

[FoxProProgrammer]

Hi rac2,

I have been working on this database on and off the past few weeks. Unfortunately this isn't my only task, and it has been more difficult because I haven't been able to focus on it. That said, I made massive changes to the table structures and forms to integrate the two databases together. That necessitated changes to the VB code behind the forms, so I ended up starting from scratch - almost. I'm almost there, but have a few issues to work out.

Here's how the structure looks now. I implemented your idea that the jobs table not have any fields related to the thing being repaired.

tbl_jobs
- jobID (primary key)
- jobNum
- dateCompleted

tbl_LRU_repair (Note that some of this data was formerly in tbl_jobs)
- LRU_RepairID (primary key)
- JobID (foreign key to tbl_jobs)
- LRU_repair (foreign key to tbl_LRU)
- LRU_SN (serial number of LRU)
- comments
- NFF
- customer
- aircraftID (foreign key to tbl_aircraft)

tbl_LRU_parts_replaced
- jobID (foreign key to tbl_jobs)
- partID (foreign key to tbl_SRU)
- partSN (serial number of SRU replaced)

tbl_SRU
- SRUID
- Description
- PartNum

tbl_LRU_chassis_parts
- PartID
- Description
- PartNum

Other tables in the database relate tbl_SRU to parts on circuit boards. I didn't include them in this post because you probably already have information overload! There are two primary reasons that I did not include the parts in tbl_LRU_chassis parts in tbl_SRU.

1. The chassis parts are not repairable items. They are replaced only whereas all the parts in tbl_SRU can be repaired. This solved one of the problems that we discussed a few weeks ago related to whether I needed to include a field that identified an item as repairable or not repairable. An example of a chassis part is a relay inside the box. An example of an SRU is a circuit board inside the box.

2. tbl_SRU is related to a set of tables that contain foreign keys to components on the circuit boards (SRUs). It wouldn't make sense to include chassis parts in this table because they don't relate to these other tables.

So, now my questions.

1. How can I relate tbl_LRU_parts_replaced to tbl_LRU_chassis_parts? I thought about adding a field to tbl_LRU_parts_replaced that would relate to PartID in tbl_chassis_parts, but that doesn't seem like a good idea because the same job can appear more than once in tbl_LRU_parts_replaced.

2. After I figure out the answer to question 1, I will need to write a Query (Union?) to retrieve SRUs and Chassis parts. The combined result will be presented in a list box that is displayed when the user selects an LRU from a combo box. Right now, only the SRUs are displayed in the list box.

Thanks so much for all your time.

Best regards,



dz

 

[FoxProProgrammer]

Wanted to follow up...

I created a new table called tbl_chassis_parts_replaced. It has only two fields: jobID and partID. jobID is a foreign key to tbl_jobs and partID is a foreign key to tbl_LRU_chassis_parts. I wrote a Query that almost works. The discussion of the Query itself might be better suited for the Query forum. If you have any suggestions regarding my table structure, I'd be very grateful.

Thanks again,

dz