Still Struggling 1

[tekbro]

I see where you're coming from. We already use some PLM software at my company-namely SAP and Enovia PM. The problem is that it's hard to get someone set up on these. As a result, only a few folks have permissions or training to use these tools. On the other hand, almost everyone has Access on their desktop.

Keep in mind that the drawings I'm talking about are just TIF images of our vendor drawings. We don't do actual changes to the ACAD source files. They send us a flat image of the drawing which was exported from their drawing system. We log it in our spreadsheet, noting the date and other attibutes of the drawings. We'll review the drawings and provide comments to the vendor. Depending on the comments, the supplier may just update minor editorial issues in-house, and that's the last we'll hear of it. However, with technical changes, the supplier will send the corrected drawing back to us to validate that our comments were satifactorily addressed, and for approval. The drawings aren't linked in any elaborate part cataloging scheme. Although now that I think about it, we do have such a catalog/drawing/diagram/model configuration management system for drawings that WE generate for stuff that WE make ourselves) Just not for drawings for stuff made by our suppliers.

Currently, my vendor drawing image spreadsheet is a flat file with everything in one table:

DrawingNumber, Title, Revision, RevisionDate, SubmittalDate, SubmittalLetterSerialNumber, Notes/Comments, VendorCAGE, Category, Sheet Number, TotalNf Sheets, etc.

In my database, I have separate tables for:
Drawings, VendorData, RevisionData, Status, Personnel

My plan was to relate these tables using PK, Foreign Key relationships, creating table joins when needed in queries.

Instead of a table or report like:
DrawingNum, DrawingTitle, Rev1, Rev1Date, Rev2, Rev2Date...

I'd rather have something like:
Drawing Number Drawing Title SheetNo. CurrentStatus
123G456 Main Assembly - Approved
Current Revision
-Revision RevisionDate RevisionDescription
B 3/26/08 Changed pipe size in grid A4
B 3/26/08 Increase flange size to 2.5"
-Revison History
Revision RevisionDate RevisionDescription
A 2/8/08 blah blah
A 2/8/08 more blah
N/A 12/5/07 Preliminary submittal

In other words, I don't want to just display the current rev of the drawing, and obliterate the revision history. I want to preserve the revision history somehow. I'm thinking it may be a good idea normalize the Revision table further and make a separate revision description table. Am I proceeding the right way?

 

[plmguy]

Well, I think you have the right plan. You'd definitely want to retain and display each document's complete revision history, not just the most recent revision.

Here’s a basic outline of a minimal set of tables. I don't recommend this narrow approach for a serious PLM system, but it'll be fine for your purpose (* are PKs, # are FKs):

Company table

*CompanyCAGE
CompanyName
Address
Website
CompanyPhone, etc.

DocType table

*DocumentTypeName
DocTypeDescription
NextDocumentNumber (depends on your numbering scheme)

Person table

*PersonName
#PersonCompanyCAGE
PersonPhone
PersonEmail, etc.

Document table

*#DocCompanyCAGEOwner
*DocNumber
*#DocTypeName
#CreatedByPerson
DocumentTitle

Revision table

*#DocCompanyCAGEOwner
*#DocNumber
*#DocTypeName
*Revision identifier (A, B, C,...)
LifecycleStatus (prelim, proto, production, etc. [preferred as #FK to Lifecycle table])
#RevisedByPerson
#CheckedByPerson
RevisionReleaseDate
RevisionReleaseECO (also could be #FK to Changes table)
RevisionCancelDate
RevisionCancelECO (also could be #FK to Changes table)
RevisionDescription
RevisionZone
RevisionFileName, etc.

The number of tables in this schema could rapidly expand as you add functions. But the DB schema's the easiest task; creating a useful user interface will take considerable time and effort. (You’ll find that Access has its own limitations which, in a multi-user environment, will become frustrating. But that set of problems is probably at least 18-24 months away.)

I hesitate offering these suggestions because your current SAP or Enovia PLM solutions provide all the capabilities that you’ll be duplicating in Access. It's one thing to re-invent the wheel, but a bit disheartening to help someone re-invent a wheel that's already available. Even if your users' current PLM system access is limited due to license expense or training complexity, splitting your documentation into "heavy duty" and "casual use" product data systems seems extremely unfortunate.

A complex PLM system that exceeds your company’s needs or resources can be almost as bad as no system at all. Perhaps you might consider converting to a low-cost PLM system that everyone can use, and where you can manage all of your information in one place. Shouldn't that be the goal?

Good luck!