[AccessD] Parts and assemblies design

Eric:

Shameless plug for my manufacturing system.  You can check it out at
www.ezmrp.com

It will do the structured bills of material that they need.

I just added a Capacity Requirements Planning module to the system which
does not yet show on the web site however.  In the CRP module you define
work centers and then add routings for each assembly - queue time, set up
time and unit run time for each operation.  Although it's not part of their
immediate requirement, as a bonus, the CRP Calculate program will calculate
how much of each work center's capacity is being used every day.

Once they have that database built, since EZMRP is written in Access, and
since the back end is wide open, you could easily write a report in their
custom format, although E-Z-MRP does have a traveler report which shows the
start date for each operation based on a specific work order, that work
order's due date, and the assembly's lead time and, actually, the report you
model there looks mostly like an indented bill of materials, which, of
course, E-Z-MRP does.

Starting with E-Z-MRP's framework might save you a LOT of development time.
(BTW, the audit trail that it produces on each inventory transaction, to
which you can attach a serial or lot number, also goes along way towards
satisfying FDA requirements.)

If you're interested after checking the web site, please contact me off-line
at bchacc at san.rr.com and I'll give you a system to evaluate.  Or call if you
want to talk about it - 858-259-4334.

HTH

Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com




----- Original Message ----- 
From: "Eric Goetz" <EricGoetz at egisystems.com>
To: <AccessD at databaseadvisors.com>
Sent: Sunday, September 05, 2004 11:32 AM
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