Have you ever had a look at the GENERATE_UNIQUE () function in SQL?
Excerpt from the SQL reference:
The GENERATE_UNIQUE function returns a bit data character string 13 bytes
long (CHAR(13) FOR BIT DATA) that is unique compared to any other execution
of the same function. The function is defined as non-deterministic.
I use this unique key in all of my tables that need a universally unique
key, i.e. different customers write into the same table and finally we have
to merge all this tables together.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Richard Schoen
Sent: Montag, 14. Dezember 2020 00:03
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: The age-old database design question - Integers or GUID/UUID for
primary keys ? What are your thoughts ?
I'm curious what the current thought process is for designing new databases
and primary indexes ? This is not RPG specific so I'm posting in the
Midrange List Forum.
I feel it pretty much boils down to this: use incrementing integer primary
ID fields or GUID/UUID values for primary keys.
Back in the early IBMi/AS400 days we had the Relative Record Number for
uniqueness if needed and typically used some sort of unique value like
customer number, state code, etc. That was before SQL Server, MariaDB,
Postgres and other databases came along.
In later development pursuits using SQL, the integer or UUID/GUID primary
key pretty much took over the role for unique record ID. However using
UUID/GUID often gets maligned because it can slow down query and insert
processing.
Using UUID/GUID keys does enforce pretty much inherent uniqueness,
especially when databases may need to be merged.
But we experienced issues with SQL Server databases that would get into the
tens of millions of record sizes. Often this was probably hardware related,
but these types of DB issues are tough to diagnose because every customer
system had different specs.
There seems to be varying opinions on primary indexes these days, but
nothing definitive. I wanted to read something relatively current related to
MariaDB and found the article below.
https://www.peterspython.com/en/blog/using-uuids-instead-of-integer-autoincr
ement-primary-keys-with-sqlalchemy-and-mariadb
I'm curious on other thoughts for proper database design.
As an example here's a hypothetical customer table example. How would you
index this if you wanted to search on Customer Number and Name ? My take is
below.
CUSTOMERS
-ID - GUID - (Primary Key)
-CustNumber - VARCHAR(100) - (Actual Customer Number. Unique, but soft coded
for change) -CustName - VARCHAR(100) - (Not Unique. Uniquenedd enforces via
Customer/ID combo)
Let's say I want to optimize the table for searching. My thought would be to
have the following indexes:
-Primary_Key - ID (Insures no overlapping primary key values)
-CustNumber_Key - CustNumber,ID (Unique to avoid same customer# in table)
-CustName_Key - CustName,ID - Not unique. Used for name searching.
I'm curious to hear others philosophies on the topic of database design,
primary keys and indexing for swift retrieval and updates.
All input appreciated.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net<
http://www.richardschoen.net/>
Email: richard@xxxxxxxxxxxxxxxxx<mailto:richard@xxxxxxxxxxxxxxxxx>
--
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
