How do I guarantee inventory numbers are unique across all tables?
First insert a row in a table of "objects" that includes an AUTO_INCREMENT PRIMARY KEY.  Get the id by using LAST_INSERT_ID().

Then insert whatever rows you need into other tables.  Use the id from the first step as the unique id.

This works for MySQL and MariaDB.  Other databases have the concept of a SEQUENCE.  Such can be simulated in MySQL/MariaDB, essentially by using the 'first' step I gave you.  So, if you really need to be somewhat db-independent, start with a SEQUENCE emulation.

(My opinion:  There are so many differences between RDBMS vendors that it is folly to try to have one code for all.)