After working for many years in the financial services realm I recently
started working for a small manufacturing / wholesale firm. Recently a
project was kicked off which will use Access 2007 to store “Part
Information”. One of the existing practices is to occasionally change
part-numbers. For example Part ABC-100 might be changed to ABC-200 and the
key ABC-100 may be used for another part. This practice has evolved over
several years and there are probably good reasons for it. (Currently
procedures are primarily manual with some Excel spreadsheets.)

With the introduction of Access and storing the part info in the database, I
am making the case that this practice is likely to cause many problems down
the road.

Some people are questioning the wisdom and the need to abandon the current
practice. Here is one example. There is a white envelop that measures 6” by
10” and the part-number is ABC-101. A switch is made to a new supplier and
part ABC-101 is replaced by an off-white envelop that measures 6” by 10.1”.
People wonder if we really need to change the part number for such minor
changes. Without a background in manufacturing or wholesale operations, I am
not sure how to answer this type of question.

Are there any generally accepted “best practices” that would apply to this
issue?

Any insight would be most appreciated.

Thanks,
Brad

One rule of thumb is that a primary key shouldn't change. One way to do this
is by assigning an artificial primary key, such as an autonumber, to join
tables instead of a natural primary key with 'meaning'. If the meaning
changes, there can be problems when using natural primary keys.

In your case you could use something like an autonumber. You could also have
a field with the natural information. You could also have a yes/no field
named something like CurrentPN. That way you could reuse part numbers but
only keep the latest in queries.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Brad" wrote:

> After working for many years in the financial services realm I recently
> started working for a small manufacturing / wholesale firm. Recently a
> project was kicked off which will use Access 2007 to store “Part
> Information”. One of the existing practices is to occasionally change
> part-numbers. For example Part ABC-100 might be changed to ABC-200 and the
> key ABC-100 may be used for another part. This practice has evolved over
> several years and there are probably good reasons for it. (Currently
> procedures are primarily manual with some Excel spreadsheets.)
>
> With the introduction of Access and storing the part info in the database, I
> am making the case that this practice is likely to cause many problems down
> the road.
>
> Some people are questioning the wisdom and the need to abandon the current
> practice. Here is one example. There is a white envelop that measures 6” by
> 10” and the part-number is ABC-101. A switch is made to a new supplier and
> part ABC-101 is replaced by an off-white envelop that measures 6” by 10.1”.
> People wonder if we really need to change the part number for such minor
> changes. Without a background in manufacturing or wholesale operations, I am
> not sure how to answer this type of question.
>
> Are there any generally accepted “best practices” that would apply to this
> issue?
>
> Any insight would be most appreciated.
>
> Thanks,
> Brad
>

My background is running smaller engineering and manufacturing intensive
companies where we deal heavily in part numbers.

Regarding your database question, I would echo Jerry's answer. Anytime
that a field can be "messed with" by outsiders (in your case, the part
number) I would recommend not having it be the PK.

Regarding your company's number changes, it's behavior per your first
paragraph "For example Part ABC-100 might be changed to ABC-200 and the key
ABC-100 may be used for another part. violates "Part Numbering 101" and
basically makes the part numbers meaningless.

The description of behavior in your later paragraph "There is a white
envelope that measures 6” by 10” and the part-number is ABC-101. A switch is
made to a new supplier and part ABC-101 is replaced by an off-white envelop
that measures 6” by 10.1”. " is incomplete/ambiguous regarding what you are
actually doing in that case and so I can't comment on it.

Your company needs to start by defining what "part number" actually means to
it...that definition should have some enduring aspect, and then they should
follow that definition. The vague outline for this is that part number
includes a definition (which never fundamentally changes) that nails down
enough to fulfill a certain form fit and function. "Form, fit and function"
could be very loose or very tight, here are some examples:

Loose: Where the part number is a "sales number" that is exposed to your
customers. And the only enduring definition is that it can anything that
nails down attributes that are relevant to customers.

Medium: Nails down form, fit and function for manufacturing purposes.

Tight: Nails down every attribute, including vendor etc.

Brad,

My response is more from the business side, not from the database side.
(Jerry and Fred are correct in what they said.) My background is in 30 years
of business analysis, system auditing, system design, and programming, and
product support.

When you are doing things manually, you can change whatever you want.
However, when you are automating processes you are also building a history of
data. So a year from now if you run a report, it should be a duplicate of
what you ran the previous year. If you start changing part number or
description, those old reports will now contain different information.

Let's say you re-run an inventory report form a year ago because of some
audit questions (and you built the file per Jerry and Fred's
recommendations). That report will list today's part number or today's
description of the part that you sold a year ago, not the actually part
number or the actual part description that you sold a year ago.

The following example assumes you follow the suggestions that Jerry and Fred
made:

A year ago, you have a part number called ABC-100 which is the white 6" by
10" envelope. The key to this record is an autoassigned number of 5. Six
months later, you change the part number of ABC-100 to ABC-200 AND they
re-assign the old part number to a different department. You then reuse the
existing part number of ABC-100. The key to the new record is autoassigned
number of 6.

Should you re-run your reports from prior to the change dates, they will now
be different! ESPECIALLY if the changed the department number assigned to
the part and your reports total by department! The totals will be different
by department.

I realize that you did not say you would change the department (if you even
have one), but changing "base" information (such as a part number or part
description) WILL affect your historical reports and sometimes in a very bad
and unexpected way.

I once had to unravel a $10 MILLION dollar accounting error because the
people who were using the system where constanting changing the values of
what different codes meant over a period of time. It took twenty people and
three months of research to unravel.

The business reason NOT to do this is because it could unexpectedly change
your historical reports and have unforeseen impacts down the road. I realize
that you need to change the corporate mind set on this issued and sometimes
that is impossible to do. (I know, I have hit my head again that wall many
bloddy times.).

I don't know if you wil have historical report or the ability to re-run
reports from prior months or years. But this will be a concern.

Personally, I believe that due to affects on historical report, I believe it
is a BAD practice to change the meanings of your codes. So when people
question the "wisdom" of changing a bad practice that is like saying "I
question the wisdom of stopping me from reading the newspaper while I'm
driving since I've never had an accident while reading the paper."

From a database stand point, this is not a big deal. From a business stand
point, I believe it is a big deal.

Ok, I'll give you a different example. What happens if you sell a couple
boxes of ABC-100 while 6 x 10 envelopes. Then you change your product code
ABC-100 to mean something else and ABC-100 gets change to ABC-200. A month
latter, the customer returns your box of ABC-100, except ABC-100 is not
longer the part number. What do you do now?

These questions and issues are just the tip of the iceburg. Yes, you are
right to question the LACK of wisdom of the current practice. Yes, you are
right in recommending the need to abandon the current practice! I strongly
believe that this is a bad business practice.

Good luck.

Dennis.

Jerry, Fred, Dennis,

Thanks for your help and insights.

You guys are great!

You confirmed my ideas and gave me more enthusiasm and a broader perspective
as I move forward with my push to discontinue our current practice of
changing part numbers.

Thanks again, I really appreciate your assistance.
Brad



"Dennis" wrote:

> Brad,
>
> My response is more from the business side, not from the database side.
> (Jerry and Fred are correct in what they said.) My background is in 30 years
> of business analysis, system auditing, system design, and programming, and
> product support.
>
> When you are doing things manually, you can change whatever you want.
> However, when you are automating processes you are also building a history of
> data. So a year from now if you run a report, it should be a duplicate of
> what you ran the previous year. If you start changing part number or
> description, those old reports will now contain different information.
>
> Let's say you re-run an inventory report form a year ago because of some
> audit questions (and you built the file per Jerry and Fred's
> recommendations). That report will list today's part number or today's
> description of the part that you sold a year ago, not the actually part
> number or the actual part description that you sold a year ago.
>
> The following example assumes you follow the suggestions that Jerry and Fred
> made:
>
> A year ago, you have a part number called ABC-100 which is the white 6" by
> 10" envelope. The key to this record is an autoassigned number of 5. Six
> months later, you change the part number of ABC-100 to ABC-200 AND they
> re-assign the old part number to a different department. You then reuse the
> existing part number of ABC-100. The key to the new record is autoassigned
> number of 6.
>
> Should you re-run your reports from prior to the change dates, they will now
> be different! ESPECIALLY if the changed the department number assigned to
> the part and your reports total by department! The totals will be different
> by department.
>
> I realize that you did not say you would change the department (if you even
> have one), but changing "base" information (such as a part number or part
> description) WILL affect your historical reports and sometimes in a very bad
> and unexpected way.
>
> I once had to unravel a $10 MILLION dollar accounting error because the
> people who were using the system where constanting changing the values of
> what different codes meant over a period of time. It took twenty people and
> three months of research to unravel.
>
> The business reason NOT to do this is because it could unexpectedly change
> your historical reports and have unforeseen impacts down the road. I realize
> that you need to change the corporate mind set on this issued and sometimes
> that is impossible to do. (I know, I have hit my head again that wall many
> bloddy times.).
>
> I don't know if you wil have historical report or the ability to re-run
> reports from prior months or years. But this will be a concern.
>
> Personally, I believe that due to affects on historical report, I believe it
> is a BAD practice to change the meanings of your codes. So when people
> question the "wisdom" of changing a bad practice that is like saying "I
> question the wisdom of stopping me from reading the newspaper while I'm
> driving since I've never had an accident while reading the paper."
>
> From a database stand point, this is not a big deal. From a business stand
> point, I believe it is a big deal.
>
> Ok, I'll give you a different example. What happens if you sell a couple
> boxes of ABC-100 while 6 x 10 envelopes. Then you change your product code
> ABC-100 to mean something else and ABC-100 gets change to ABC-200. A month
> latter, the customer returns your box of ABC-100, except ABC-100 is not
> longer the part number. What do you do now?
>
> These questions and issues are just the tip of the iceburg. Yes, you are
> right to question the LACK of wisdom of the current practice. Yes, you are
> right in recommending the need to abandon the current practice! I strongly
> believe that this is a bad business practice.
>
> Good luck.
>
> Dennis.