[AccessD] Parts and assemblies design

Eric,

 

You are getting into the basics of a Material Resource Planning system
(MRP).  You are making a Bill of Material (BOM) structure.  I would
recommend that you find some examples of these before you do much more work.
(Sorry I can't give you direction on this.)  I suspect though, that using
many-to-many relationships are not what you will want.

 

Also, since you are developing a system for a medical device company, you
must be very familiar with the FDA's rule on electronic records and
signatures.  This is called Title 21 CFR Part 11.  Much information is on
the FDA's website, 

 

http://www.accessdata.fda.gov/scripts/cdrh/cfdocs/cfcfr/CFRSearch.cfm?CFRPar
t=11

 

Also, there are some guidance documents issued by the FDA over the past
couple of years that will have an impact on what you're doing.

 

The Regulatory Manager at your company should be able to give you more
information.

 

Best of Luck!

Dan

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Goetz
Sent: Sunday, September 05, 2004 1:33 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Parts and assemblies design

 

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

 

 

 

 

 

--

_______________________________________________

AccessD mailing list

AccessD at databaseadvisors.com

http://databaseadvisors.com/mailman/listinfo/accessd

Website: http://www.databaseadvisors.com