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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|