In the new tables we make we tend to use a surrogate key generated by the database as the unique row identifier and this is used as a foreign key in related tables (in one specific case we generate the ID ourselves using CMPSWP to avoid problems with concurrency). I think this is the way most non-IBM i databases are created, I also use this approach in all of my MariaDB databases and I have the feeling that natural/composite keys as used in most old IBM i designs are considered a bad thing outside of our world. Using the RRN for such things was never a good idea for this purpose as, if I remember rightly, it would change after a reorganise or a file restore etc. which would be disastrous.
The surrogate key is different to the natural business key(s) which are enforced through separate indexes to the primary key.
For example, something like this.:
CUSTOMER_TABLE (CUS_ID, CUS_NUMBER, CUS_NAME, CUS_ADDRESS....)
CUS_PK_IDX over CUS_ID (UNIQUE/PRIMARY KEY)
CUS_UNQ_NUMBER_IDX over CUS_NUMBER (UNIQUE)
CUS_UNQ_NAME_IDX over CUS_NAME (UNIQUE)
ORDER_TABLE (ORD_ID, CUS_ID, ORD_NUMBER...)
ORD_PK_IDX over ORD_ID (UNIQUE/PRIMARY KEY)
ORD_UNQ_NUMBER over ORD_NUMBER (UNIQUE)
ORD_CUS_FK_CONSTRAINT over CUS_ID = CUS_ID (CUSTOMER_TABLE)
So here the xxx_ID columns are the surrogate keys and have unique, primary key indexes. The CUS_ID column in both tables is the common key, and it's set as a foreign key constraint in the ORDER_TABLE. The other indexes enforce the business rules (e.g. no two customers can have the same name or the same number and no two orders can have the same number). Notice that the natural/business keys are *not* used to join the tables, only the surrogate key are used for this.
The downsides of doing this is, as alluded to, is that copying data to other systems or merging the data from a live DB to a test DB can be difficult if the IDs cannot be guaranteed to be globally unique.
Here's quite a good article discussing the various types of key Database Keys: The Complete Guide (Surrogate, Natural, Composite & More) - Database Star<
https://www.databasestar.com/database-keys/>
Tim.
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
Sent: 14 December 2020 15:07
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: The age-old database design question - Integers or GUID/UUID for primary keys ? What are your thoughts ?
Thanks for the thought-provoking input.
I guess the cool thing about using bigint would be that it mimics the relative record number concept that we're used to being spoiled with on IBM i if we've worked on DB2 long enough 😊 Plus it can be auto-incrementing. And the secondary extended value can be used for merging or other purposes where the true key such as customer# or systemid if important to have.
If the ID isn't needed for secondary indexes (like customer# ) and joining, then if the data is moved to another system the ID doesn't really matter (because it's essentially like a RRN) if I were to move the data, correct ? I would be relying on the secondary keys for my meaningful joins.
Just want to make sure I’m thinking of this correctly. And database indexes (on other platform databases) are essentially replacing my logical files.
BTW: I think your AppServer4RPG might be the perfect medicine for customers implementing MariaDB and Postgres on IBMi. Allowing them to use the data from RPG. I plan to create a few samples soon.
Happy holidays.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------
message: 3
date: Mon, 14 Dec 2020 09:32:09 +0100
from: "D*B" <dieter.bender@xxxxxxxxxxxx>
subject: RE: The age-oi ld database design question - Integers or
GUID/UUID for primary keys ? What are your thoughts ?
@UUID:
The maria paper you mentioned says: "The probability of a collision is almost none."
A few years ago DB2/400 stated: "UUIDs are most likely unique"
The reason for this is, that a part of the UUID depends on timestamp. This could be solved by a function (provided by the datatbase, mentioned from Birgitta; or by a UDF) or has to be solved in the application layer in the insert process. Uniqueness is guaranteed anyway by the primary key.
@merging data:
This is done for UUIDs by a part of the key is dependend on the box it is generated from. Same could be done for the number keys: just add another key column for the customer or the box the record is inserted from.
@your indexes:
The ID is not needed as a part of your CustNumber_Key and CustName_Key. The ID is only needed for the elementary join operations and normaly ensured by your referential constraints
@how to decide:
It depends on your needs! For heavy transactional workload of the insert and delete (!!!) processes, I would do extensive workload tests first. In a datawarehouse project (it was the biggest real world database on as400 in germany of those days) the decision was to use bigint keys, generated in the application layer (blocking in the process outperformed autoincrement numbers - maybe dependend on the current implementation).
@some thoughts:
- index size is very important!
-- indexes could get bigger than the tables
-- delete performance of reorg processes depends on index size
- bulk operations could be speeded up by parallelism and are influenced by your index decision
@my preference:
Bigint keys optionally extended by a key for the source-system
D*B
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
