Mysql – How to guarantee inventory numbers are unique across all tables

database-designMySQLsubtypes

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords to be inventoried and 'butters' for different butters. Obviously, these two objects have practically nothing in common as far as attributes other than the fact that they both need to be accounted for in the inventory. This is why they each will have their own table.

I need every inventoried object to have a unique, integer ID assigned to it. So, inventory number '5' might be a sword and '6' might be a butter.

I believe what I need is a separate table which just keeps track of inventory numbers, 'inv_nums'. Then I'd have a foreign key constraint referencing that table in each of the 'swords' and 'butters' tables. The relationship would be 1:1 between an object table and the 'inv_nums' table.

How do I guarantee that an inventory number doesn't get used, inadvertently or otherwise, by multiple object tables?

I'm thinking maybe I need to use a trigger on inserts which calls a function or procedure (not sure which) to either create and return the next sequential inventory number or ensure that the one being provided in the insert statement isn't already in use (it doesn't already exist in the 'inv_num' table).

Am I on the right track? I'm sure there must be a design pattern for this somewhere but my googling hasn't been very successful thus far. I could certainly do all this at the application layer but I'd rather have it enforced in the DB itself.

I'm implementing this in MySQL/MariaDB but I think the concept behind this should be the same no matter the (relational) DB.

Best Answer

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.)

Related Question