Re: The age-old database design question - Integers or GUID/UUID for primary keys ? What are your thoughts ? -- MIDRANGE-L

In regard to "proper database design", the rules of normalization have a
much bigger impact on the performance and flexibility of databases than
whether you use auto-increment or globally-unique keys. However, my
preference is to use auto-increment surrogate keys, where key values are
assigned by database-insert event handlers rather than within application
code, and follow a field naming convention. For example, if the name of the
table is say EORG100, then the name of the primary key would be EORG100K.
Then any time you find EORG100K in any other table, then you know it refers
to a foreign-key relationship back to the EORG100 table.

On Sun, Dec 13, 2020 at 4:03 PM Richard Schoen <richard@xxxxxxxxxxxxxxxxx>
wrote:

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-autoincrement-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