Designing tables for drawing revision control database

[tekbro]

Hi! I work for a company that has machinery built by outside vendors, and re-sells the equipment to its customers. Currently we track our drawings using an Excel spreadsheet, which is a disaster waiting to happen.

I've been asked to design an Oracle database that will informally and simply track and report the status of our company's vendor engineering drawings. I'm more familiar wiht Access, so I'm going to get it working in Access now and then try to make it work in Oracle or MySQL later.

The database should store the revision history of each drawing, vs. just giving you whatever the current revision is. Another optional feature for future development is tracking revisions to individual sheets of the drawing vs. the entire drawing.

First of all, I want to make sure that I'm designing my tables correctly. Here is what I have so far:

tblDrawings
DrawingID(PK)
DrawingNumber
DrawingTitle
Sheet
TotalSheets
SubmittalDate
SubmittalDocument

This represents all of the properties of a drawing that a pertinent, except for two other fields:
RevisionLetter
VendorCAGECode

Now, I guess I could have added these to tblDrawings, I decided to make these additional tables instead, to try and normalize the tables:

tblRevisions
RevisionID(PK)
RevisionLetter
RevisionDate
Description

tblVendors
VendorID(PK)
VendorCAGEcode
VendorName
VendorAddress
VendorPhone
VendorPOC

tblPersonnel
EmployeeID(PK)
Name
Dept
Position

So I want to be able to define several properties for a drawing object, including:

1. Revisions to individual pages (children) of a drawing
2. Revisions to the Drawing and any of its individual pages
3. A description of each change that makes up a revision, so that a single Record in tblRevisions can have several comments associated with it (Do I need another table called Comments?)

So a drawing can have several sheets, a drawing can have several revisions, a drawing can have several sheets and a sheet can have several revisions, and any revision can have several comments.

Am I on the right track, or am I making this harder than it has to be?

 

[KenReay]

Hi

Looks reasonable, but I do not see the link between Drawing and Revision

Something like:

tblDrawings
DrawingID(PK)
DrawingNumber
DrawingTitle
Sheet
TotalSheets
SubmittalDate
SubmittalDocument

tblDrawingSheets
SheetId (PK)
DrawingID
SheetNumber
Description
... etc

tblRevisions
RevisionID(PK)
DrawingId <-- not totally necessary, unless you need to
SheetId have revions at Drawing Level
RevisionLetter
RevisionDate
Description


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[tekbro]

Thanks! I'm happy to see that I wasn't TOO far off the mark. But let me take it one step further. Sometimes our vendor submits the same revision of a drawing via separate submittals. For instance. In January, he sends in drawing 123G456, Revision "-" as submittal number 1.1. We give him some updates and he send in Revision "-" again, with our comments incorporated under submittal 1.1, Rev. A.

So basically, we've got the same drawing, with the same revision, but submitted twice. So there are now two VERSIONS of the same revision.

So far, I've been using a surrogate key (Drawing ID) as an identifier for my drawings. BUT, perhaps that isn't enough. I'm thinking it might be smart to develop a composite key, the combines the drawing number, the drawing revision, and the submittal number (or the submittal date) as the true identifying key.

Would this approach be off-base?

 

[ck1999]

Just for quality purposes how can you have two versions of the same revision? Should not the second revision go in under 1.2 or 1.1 Rev B

Also, How would someone looking at this database know which version to send to a new vendor?

ck1999

 

[tekbro]

YES! That is a very good point! Indeed, an interim version should be treated as such, and not considered released until we have the final, agreed upon version. And that version should then become a new revision.

Sometimes, our vendor plays fast and loose with how he revisions his drawings, so we try to establish an convention to accommodate this an keep track of interim and officially released versions.

For the purpose of this database, I think I should keep it simple and assume that everyone will play nice. By that, I mean each revision should be unique and not have "children" revisions.

Now going back to the linking revision and drawings information:

I'm having a real time with this, especially figuring out how to make the form. Basically, I'm wondering how to enter my drawing in the main drawing table. Lets say I have drawing A1234, Rev. A. Now, lets say I want to enter revision B. I think I should enter a new line under rev A, with the same drawing number, only with B in the Revision field. But the DrawingID number will be unique.

So when I design the form, I'm thinking at the top, I'll display the drawing number and title. Below that, I'll have a sub-form that displays ONLY the revisions for the CURRENT drawing. When I page down to the next drawing in the list, the revision field should change to display only the rev information for THAT drawing.

Follow me?

 

[ck1999]

I follow what you are saying but not sure what you are asking!

You may or may not want to also have a subform your your sheets table if you are following kenreay suggestions.


ck1999