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
-Revision RevisionDate RevisionDescription
B 3/26/08 Changed pipe size in grid A4
B 3/26/08 Increase flange size to 2.5"
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?