Simple Engineering Drawing Database 1

[tekbro]

I'm a new member to the site so first off, hello everyone! I appreciate all of the helpful advice and tips that I've found on eng-tips through the years.

My question may sound familiar to some of you so consider it a "nod" to the old thread781-92819 (now closed to replies).

I too, am trying to build a **simple** drawing database for a product line that I'm working on. It will only reference about 3000 drawings before all is said and done.

Currently, we're listing our vendor drawings as a flat-file spreadsheet-just one big table. From a convenience standpoint, this is great. You just open the file and type away. From a configuration management and data integrity standpoint, it is well-potentially disastrous. We've been lucky so far.

I've been asked to make our spreadsheet more functional and more secure, but without spending any money or paying for IT development time. Because I've been cursed with a few Access training courses (and was stupid enough to tell someone), I am the unlucky so & so that gets to accomplish all of this.

So, we are tracking the usual suspects in our drawing table like: Drawing Number, Title, vendor CAGE code, revision, revision date, release status, customer approval record, approval date, system, cognizant engineer, etc.

Starting at the top, I immediately began thinking of how to normalize my tables, and reduce duplicate data as much as possible. Upon doing this, I noticed an immediate problem:

To normalize my table, I think I should separate my drawing data and my revision data. But I also need to maintain a revision HISTORY of each drawing for control purposes. I'm not sure how or where to store all the old revisions in a drawing revision field, especially when the field may have to contain several values. And then, how do I make the user aware of any previous revisions-subforms and subreports?

I'm pretty sure I know what I want this database to "look" like, but telling MS Access how to give it to me is turning out to be the hard part! Please help...

 

[plmguy]

Congratulations! This is your first step to spending many hours learning how to design and build your company's product data management ("PDM") system. Writing your own PDM software can be a surprisingly long road because it starts, as you'd expect, with just a few tables. But then the boss decides it'd be great to manage part data, and also approved supplier components. (Is that one more table or two? Actually, it's at least four.) Then there's only a short step to managing bills of materials (don't forget the Units of Measure table). These data elements should all be controlled using change forms, and sooner or later your boss will want approval workflow with email notifications. And if you've come this far, "where used" is practically free, assuming you've implemented your search functions well. With all this capability, auto-generating part and document numbers will be required, as will data export to Excel or PDF. Multi-level BOM reporting is just one recursion procedure away, and doing a product cost roll-up should be easy (you did add unit costs to your part records, right?).

Let's get the simple stuff out of the way: You're correct, you'll want to represent documents in 2 separate tables. The first contains the unchanging "essence" of the document, minimally the identifying number, the type and title. The second table contains the evolutionary data, such as revision, release (and cancel) dates, as well as releasing (and canceling) change forms like ECNs.

But just in these two tables, you'll also want to consider whether there should be separate tables for the document Type (yes, there should), the author/engineer (you'll find a "Persons" table very useful as the system grows), and to track the ECNs (yes, again, along with a linking table for the relationship between ECN and revision; there's a separate relationship between ECR and item, but that's too complicated to get into now).

Also, when you create a new document revision, some of the information should be copied from the old revision. For instance, it's usually true that the releasing ECN & date for Revision B is the same as the canceling info for Rev A.

Of course, you'll need a user interface to create, display and maintain these relationships, and to support the reports requested by your users.

The more interesting question will be how to manage the actual CAD files. Once the database has been created, a file library would allow you to "lock down" each drawing revision, and let everyone in your company search for, open, and view the file without allowing edits. (File check-in/check-out is a separate capability.) Your boss may want only certain users to have access to unreleased or obsolete drawings, so you'll probably want to learn about user roles and security.

Although PDM software is complicated, and could grow into a significant distraction, it can also be a fun and educational project. I've helped design two Access PDM systems and a SQL Server PLM system, and have learned a tremendous amount about many subtle issues in configuration management. In 1993, our first and simplest PDM software ended up with over 40 tables:

PDM (and its slightly bigger brother, product lifecycle management, "PLM") software represent major efforts. Most people wouldn't consider writing their own CAD software because it's difficult to visualize an incremental development approach. PLM software can be envisioned as a series of incremental steps, but the total effort is still measured in tens of man-years. Incremental is not a great approach because many of the underlying issues must be anticipated to avoid "getting painted into a corner".

Before committing to a specific design approach, you might want to get familiar with how the final product should look. There are a number of books available to provide sound advice, but I recommend Vince Guess's and Thomas Samaras' books for drawing/document data management, and Jerry Clement et al. for bill of materials management:

http://www.buyplm.com/pdxpert-plm-books.htm.aspx

Your labor is paid for, but it sure isn't free - who's doing your job while you're designing the software? At some point, it's less expensive to go buy a PLM system than to create the complete product. One or two weeks of effort will get you a minimally-capable drawing tracking system, but for an equivalent expenditure in cash, you can buy a fully-functional PLM system that does everything your boss could want, and comes with reports, data export, help files, support staff and all those not-so-obvious CM rules.

But as I mentioned, if you can spare the time, it's a lot of fun!