2

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.

1

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

| improve this answer | |
-1

The code below is an (simplified) example of how I met the requirement of having a separate table for each item type (e.g. guns, butters, etc) and that every item, regardless of type, has a unique inventory number assigned to it. Items must also never share the same inv number even if they're in separate tables.

This solution is following an inheritance model which in ER modeling parlance is called generalization/specialization. In OOP you'd call it a class/subclass or parent/child model. I'm specifically using "Class Table Inheritance" for this solution.

I start with an item_types table. The type_id in this table can also be thought of as a table id; it's essentially keeping track of which table an inventory number has been assigned to. The sample data I'm inserting will assign a type_id of 1 to all guns and 2 to all butters.

CREATE TABLE `item_types` (
  `type_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`type_id`)
) ENGINE = InnoDB;

INSERT INTO `item_types` (`name`) VALUES ('guns');
INSERT INTO `item_types` (`name`) VALUES ('butters');

The items table is the parent "class" which all items will be "children" of, to use OO terminology. Note the composite INDEX in this table which will come into play in the child tables.

CREATE TABLE `items` (
  `inv_num` INT NOT NULL AUTO_INCREMENT,
  `type_id` INT NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_items_item_types`
  FOREIGN KEY (`type_id`)
  REFERENCES `item_types`.`type_id`,
  INDEX `idx_inv_num_type_id` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

The tables created for each item type are made children of the items table by having them share the inv_num primary key with items. This ensures that the inv_num value must be present in items before it can be assigned here. By creating a composite foreign key with inv_num and type_id, we can now control which table is allowed to use an inv_num value from the items table. This ensures that the same inv_num value cannot appear in both the guns table and the butters table. Be aware that InnoDB requires referenced columns in a composite foreign key to be indexed in the parent.

CREATE TABLE `guns` (
  `inv_num` INT NOT NULL,
  `type_id` INT NOT NULL DEFAULT 1,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_guns_items`
  FOREIGN KEY (`inv_num`, `type_id`)
  REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

CREATE TABLE `butters` (
  `inv_num` INT NOT NULL,
  `type_id` INT NOT NULL DEFAULT 2,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`inv_num`),
  CONSTRAINT `fk_butters_items`
  FOREIGN KEY (`inv_num`, `type_id`)
  REFERENCES `items` (`inv_num`, `type_id`)
) ENGINE = InnoDB;

At this point, it would still be possible to enter a combination inv_num + type_id into the wrong table. For example, an inventory number of 3 and type id of 1 (which represents guns) into the butters table. To enforce only a specific type_id being allowed in a specific table, that table must perform a CHECK when the data is being inserted. Unfortunately, MySQL does not support the CHECK operation so you must use a TRIGGER instead.

DELIMITER //
CREATE TRIGGER trg_guns_ins
  BEFORE INSERT ON guns
  FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(128);
  IF NEW.type_id != 1 THEN
    SET msg = concat('trg_guns_ins error: Trying to insert wrong card_type'
                     'value in guns: ',
                     cast(NEW.type_id as char));
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = msg;
  END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_butters_ins
  BEFORE INSERT ON butters
  FOR EACH ROW
BEGIN
  DECLARE msg VARCHAR(128);
  IF NEW.type_id != 2 THEN
    SET msg = concat('trg_butters_ins error: Trying to insert wrong card_type'
                     'value in butters: ',
                     cast(NEW.type_id as char));
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = msg;
  END IF;
END //
DELIMITER ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.