[AccessD] Parts and assemblies design

Eric Goetz EricGoetz at egisystems.com
Sun Sep 5 13:32:32 CDT 2004


Hi,

 

I am working on a database to replace some paper forms. I get the
feeling I'm starting more "from scratch" than I need to. So I thought
I'd ask if anyone knows of any examples of solutions for the following
problem.

 

A medical device manufacturer needs to track all the actions performed
on each device as it moves through manufacturing to meet FDA
requirements. The forms are called "travelers." The first traveler gets
created when the device is built. A new traveler gets created every time
the device returns from the field (for upgrade or repair.) 

 

The forms show each part with its revision, part number, lot number, or
other attribute. There are different attributes for different types of
parts. Such as, software has a checksum, and a revision number while a
relay has does not. Instead, a relay has a lot number.

 

Here are a couple of my challenges:

 

I am modeling parts and assemblies, where an assembly is also a part. As
the devices come back from the field, they may get upgraded with
different parts and assemblies so the hierarchy is flexible. There seems
to be a many to many relationship between parts and assemblies. So, I
made a basic parts table: 

 

Part { ID, PartNumber, Name } where ID is the primary key

Sample data: 

{ 1, 26.026, Enclosure }

{ 57, 26.002, PCA }

{ 113, 26.100, ECA }

{ 114, 26.098, Xfrm }

{ 115, 26.022, Xfrm }

{ 116, 26.021, Xfrm }

 

And I made another table to hold the many to many assignments between
parts and assemblies.

AssyPartAssignment { ID, AssyID, PartID, Name } where ID is the primary
key and AssyID and PartID are foreign keys for the ID in the Part table.

Sample data:

{ 1, 113, 57, Controller PCA }

{ 2, 113, 1, ECA Enclosure }

{ 3, 57, 114, Xfrm T1 }

{ 4, 57, 115, Xfrm T2 }

{ 5, 57, 116, Xfrm T3 }

{ 6, 57, 115, Xfrm T4 }

 

The report needs to show this hierarchy. 

26.100 - ECA

  26.026 - ECA Enclosure

    26.002 - Controller PCA

      26.098 - Xfrm T1

      26.022 - Xfrm T2

      26.021 - Xfrm T3

      26.022 - Xfrm T4

 

The same transformer, 26.022 shows up twice in the PCA assembly.

 

I'll build more tables for the different attributes of parts, such as,
software with its checksum, and parts that have only lots, and parts
that have revisions and lots. I'll use the ID key from the Part table as
the primary key for the subtype tables based on the entity relationship
chapter from the CJDate book.

 

I need to make the forms to enter these variable hierarchies. 

 

Got any suggestions that might put a little more holiday in my Labor
Day?

 

Thanks,

 

Eric

 

 




More information about the AccessD mailing list