I'll give you an example. Unique customer id. It was deemed that customer id's were only required to be up to 6 characters in length. However, we are now reaching 9999999 customers, no one ever thought you would get that many customers! They print customer id on hundreds of documents, and it is well known what it is by many units in the organization.
That key (6 character column) was used throughout hundreds of files. We now need to increase the length. What do you do? In RPG that means changing hundreds of RPG programs most likely and recompiling. In .NET we don't need to do anything, a string is a string, we don't care how long it is typically so .NET shields you from some of those types of changes.
Now, if all those hundreds of files were using surrogate integer (or better yet 64bit integer) single primary keys, we could instead increase the length on the customer id field (the 6 character one) in only ONE table. All the other tables are already using a surrogate key that can grow to trillions in length to link back to that one customer record.
This rule basically applies to anything where you need to change a data type in a table, and it is used as a reference in other tables to join. You cause yourself a lot of development pain when not using surrogate keys for everything because invariably someone always thinks of a reason to change a human readable key such as an order #, customer #, payment #, etc for one odd case or another due to an error in processing, a data modernization effort, or many other reasons and surrogate keys will shield you from a lot of those changes.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, August 28, 2017 11:49 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
Trying to see what business reason there would be to changing the customer number. Was another rule violated? Such as always use meaningless numbers? For example, decades back we use to teach a class on wire nomenclature to new employees. This was used to help explain our item numbers. Each part of the item number helped to categorize the part. Of course, this is a glaring violation of the database rule and would require you to change the part number if it got recategoried.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Justin Taylor <JUSTIN@xxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 08/28/2017 12:40 PM
Subject: RE: Database design question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
The arbitrary identify column protects you in the case the business data
key needs to change. In your example, if the customer numbers needs to
change, it would be a non-issue if you have an identify column. If the
customer number is the primary key, things get sticky.
midrange.com
