Make to order Manufacturer DB Structure

[jerrywolf]

Hi all -

we are a make to order manufacturer, and are in the process of creating an access database that can handle orders, customer history, inventory, etc.

The problem is we make all kinds of parts for our customers, and the puzzling thing for me is how to structure the database to handle revisions (revs). I could make a database like below:

tbl_Customers
tbl_Parts
tbl_PartRevs

However, in creating an order, would it be best to keep track of it using PartRevId, which would refer to the part, or use PartId with a rev text entry field?

What about for inventory, using a barcode scanner with autonumber as the primary key and barcode text? Would I use PartId or PartRevId?

It is important to keep track or revs, but what happens when there is a new part with no rev level? If I tracked orders from PartRevId, I couldn't enter an order with no rev?

Any input would be greatly appreciated. Thanks!

 

[spikepl]

First, explain in plain English what a revision is and how it relates to an order, orderlines, part and "order with no revisions" ?

 

[jerrywolf]

First, explain in plain English what a revision is and how it relates to an order, orderlines, part and "order with no revisions" ?

Ok sorry - I should have been more clear.

A customer supplies us with a drawing of what is to be made. Many times we keep inventory, and we do repeat products. A revision is related to a part, and it is whenever a customer makes a revision to a part drawing - it is clearly marked on the new drawing and all purchase orders. The problem is that when we make inventory, it could be a potentially different rev if we hold onto it for a while.

As for orders, the way we enter them is one part per order. This makes releasing to the shop floor much easier. So order could be customer Id, partid (or part rev Id), qty, date, notes, etc.

I guess my question is for the orders table, would it be best to store just the part Id and a text field for rev (I know this isn't normalized), just part rev id (which will relate to part id in parts table), or both(is this ok to do?)

 

[jdraw]

Jerry,

In very general terms your problem seems to have a large component similar to document management where revisions/changes to pages/paragraphs occur. You may benefit from researching document management/production related posts and/or articles.

You may get some ideas/concepts from this data structure.

A lot of Revision Control info here.

Also there are a number of data models at http://www.databaseanswers.org that relate to document management.
Example:
http://www.databaseanswers.org/data_models/document_control_systems/index.htm
and the related requirements
http://www.databaseanswers.org/data_models/document_control_systems/facts.htm

 

[spikepl]

So a Widget may or may not be revised and become Widget Rev. 1 ? If so then your trouble stems from the inconsistencey of sometimes having "grommets" without revisions and sometimes with revisions. Now what would happen if first order for a new Gizmo becomes, per default, Gizmo Rev. 0? Which of course is a pain if the drawing isn't Rev 0. But then, why isn't it?

 

[jerrywolf]

jdraw - your link to the BOM structure was very helpful. Thank you for that!

So a Widget may or may not be revised and become Widget Rev. 1 ? If so then your trouble stems from the inconsistencey of sometimes having "grommets" without revisions and sometimes with revisions. Now what would happen if first order for a new Gizmo becomes, per default, Gizmo Rev. 0? Which of course is a pain if the drawing isn't Rev 0. But then, why isn't it?

Good point - Rev 0 could be a default, however, then a user would manually have to add a rev 0 record, which isn't a huge deal. As for inventory control then, I guess it could be based on partid because inventory isn't necessarily tied to which rev, or is it?

 

[spikepl]

then a user would manually have to add a rev 0 record

Nope. Stupid stuff like this is what machines are good at. A new part would thus be born with a revision 0 and all subesquent changes would become Rev 1 .. Rev N etc.

isn't necessarily tied to which rev, or is it?

It is, because the physical part is the one with a revision, whereas Part and PartID are pure concepts without any physical presence, tags, if you like, that you can stick on each physical part. If you imagine a report with revisions - each revision is physical and tangible, but the immutable report title itself is just a concept

 

[jerrywolf]

Nope. Stupid stuff like this is what machines are good at. A new part would thus be born with a revision 0 and all subesquent changes would become Rev 1 .. Rev N etc.

So if I'm correct in my understanding, I would have

tbl_Parts
PartId
Customer
PartNumber
PartDescription

tbl_PartRevs
PartRevId
PartId (FK, tbl_Parts)
Rev

tbl_Orders
OrderId
Date
PartRevId
Qty

Basically, In my orders table I would have the PartRevId, which would in turn link to the Parts table. Then, for my inventory, I would have:

tbl_Transactions
TransId
Date
PartRevId
QtyChange
QtyCount
Notes

Now, what happens if we get an order for part xxx rev1, and I want to check inventory after I input rev 1, it would only see PartRevId for rev0? Maybe I am missing something. Also, if I was correct in your explanation, how would I have the computer automatically add rev0 to that table when a new part is input?
Thanks!