Smart questions
Smart answers
Smart people
Join Eng-Tips Forums
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Eng-Tips
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

tekbro (Mechanical) (OP)
26 Mar 08 15:35
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, TotalNo.Of 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?
Helpful Member!  plmguy (Electrical)
27 Mar 08 13:50
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

CODE

*CompanyCAGE
CompanyName
Address
Website
CompanyPhone, etc.
DocType table

CODE

*DocumentTypeName
DocTypeDescription
NextDocumentNumber (depends on your numbering scheme)
Person table

CODE

*PersonName
#PersonCompanyCAGE
PersonPhone
PersonEmail, etc.
Document table

CODE

*#DocCompanyCAGEOwner
*DocNumber
*#DocTypeName
#CreatedByPerson
DocumentTitle
Revision table

CODE

*#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!

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close