A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Changing Part-Number (Primary Key)



 
 
Thread Tools Display Modes
  #1  
Old February 9th, 2010, 04:33 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Changing Part-Number (Primary Key)

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.
  #2  
Old February 9th, 2010, 09:59 PM posted to microsoft.public.access.tablesdbdesign
Brad
external usenet poster
 
Posts: 943
Default Changing Part-Number (Primary Key)

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.