By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

DB Single part number multiple operations

P: n/a
I am new to this group and new to DBs. I am building a small DB for my
work. We create sheetmetal parts. Each part has a part number (e.g.,
1054471 or 50TG508506 - both formats are typical for a part number).
Any given part may have a number of operatons involved in it's
production. Each operation process has its own allotted code: shear -
1000; CNC machine - 2000; stamping press - 3000; etc.

Any given part may also have multiple operations within a process
(e.g., 1054471 includes 5 operations at the stamping press - currently
coded 3000, 3100, 3200, 3300, 3400).

My question in trying to layout the table(s) for my parts is that I
want to avoid redundancy in my first column and use each part number
as a primary key. I also want to track the number of parts produced
during any given operation. Would the most elegant design incorporate
an attribute to coincide with each step of the production process or,
as we have done in the past, should I have a separate table for each
step of the production process (i.e., Table 4000, Table 4100, etc.)
and use the part number as a primary key for each table?

Thanks in advance. I am probably making this far more complicated than
it needs to be.

Tim

Sep 14 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Ola Tim -

Just for the record, I'd recommend that you use an autonumber as the
unique key for your parts. With a complicated part number scheme like
that I'd be willing to bet that at some point there will be an
exception to the rule that will break the logic (a duplicate or a null
value). Or some damn thing...

My personal rule is to avoid creating any primary key that is data
dependent, with the exception of simple lookup tables where the system
designer can control the data values. Autonumbers are comepletely
divorced from data values which makes them pretty much perfect for
this structure.

===================
==================== =======================
| aPart | --------- |
lkPartProcess | ----|
aProcess |
|_____________________| | |
_______________________| | |_________________________|
| ptPartNumber Text(50) | ------->| ppPartNumber
Long | | | psProcessNumber Text(6) |
| ptPartDescr Text(50) | | ppProcessNumber
Long | <--------- | psProcessDescr Text (50) |
| ... (stuff about parts)
| ptID Autonum | |
ppID Autonum | |
psID Autonum |
===================
==================== ===================

On Sep 14, 3:44 am, tbrog...@gmail.com wrote:
I am new to this group and new to DBs. I am building a small DB for my
work. We create sheetmetal parts. Each part has a part number (e.g.,
1054471 or 50TG508506 - both formats are typical for a part number).
Any given part may have a number of operatons involved in it's
production. Each operation process has its own allotted code: shear -
1000; CNC machine - 2000; stamping press - 3000; etc.

Any given part may also have multiple operations within a process
(e.g., 1054471 includes 5 operations at the stamping press - currently
coded 3000, 3100, 3200, 3300, 3400).
>
My question in trying to layout the table(s) for my parts is that I
want to avoid redundancy in my first column and use each part number
as a primary key. I also want to track the number of parts produced
during any given operation. Would the most elegant design incorporate
an attribute to coincide with each step of the production process or,
as we have done in the past, should I have a separate table for each
step of the production process (i.e., Table 4000, Table 4100, etc.)
and use the part number as a primary key for each table?

Thanks in advance. I am probably making this far more complicated than
it needs to be.

Tim

Sep 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.