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>
midrange.com
