It must be stated, if not obvious. Surrogate keys must not be used for anything other than joining records together and uniquely identifying a record. The moment you expose it to end users through a report or screens, you have identified that bit of data as "human readable" and that data now has business context associated with it. The moment you do this you will have destroyed everything the surrogate key was supposed to give you.
Many commercial products you see these days use GUID's as it's primary keys. As you may know, these have the added benefit of going beyond the limitations of 64bit integers, but are really only needed for massive scale applications. Most applications would get away with 64bit integers just fine.
-----Original Message-----
From: Matt Olson [mailto:Matt.Olson@xxxxxxxx]
Sent: Monday, August 28, 2017 12:50 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD
midrange.com
