Implementing multiple incrementing keys in a table

[NotionCommotion]

Am I doing this correct and if so, can it be improved?

 

My desire is to have an incrementing key (public_id) individually for dogs, cats, and hamsters and individually for each user.  For instance, user #1 can have dogs with public key 1, 2, and 3 as well as cats with public key 1, 2, and 3, and user #2 can dogs with public key 1, 2, and 3...

 

Note that public keys should not be duplicated for a given type and user.  For instance, if user #1's highest public ID for cats happens to be 3, he deletes that cat, and then adds a new cat, the new cat's public ID should be 4 and not 3.

 

See the triggers I've added.  I am hoping to use the following query to add a dog named Fido to user 123.:

INSERT INTO dogs(users_id,name) VALUES(123,"Fido");

Thanks

-- MySQL Script generated by MySQL Workbench
-- 02/18/17 08:40:07
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `userSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`_inc`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`_inc` (
  `users_id` INT NOT NULL,
  `type` ENUM('dogs', 'cats', 'hamsters') NOT NULL,
  `public_id` INT NOT NULL,
  PRIMARY KEY (`users_id`, `type`),
  CONSTRAINT `fk__inc_users`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`dogs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`dogs` (
  `iddogs` INT NOT NULL AUTO_INCREMENT,
  `users_id` INT NOT NULL,
  `public_id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `dogSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`iddogs`),
  INDEX `fk_dogs_users1_idx` (`users_id` ASC),
  UNIQUE INDEX `public_id_UNIQUE` (`public_id` ASC, `users_id` ASC),
  CONSTRAINT `fk_dogs_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`cats`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`cats` (
  `idcats` INT NOT NULL AUTO_INCREMENT,
  `users_id` INT NOT NULL,
  `public_id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `catSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`idcats`),
  INDEX `fk_cats_users1_idx` (`users_id` ASC),
  UNIQUE INDEX `public_id_UNIQUE` (`public_id` ASC, `users_id` ASC),
  CONSTRAINT `fk_cats_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`hamsters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`hamsters` (
  `idhamsters` INT NOT NULL AUTO_INCREMENT,
  `users_id` INT NOT NULL,
  `public_id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `hamsterSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`idhamsters`),
  INDEX `fk_hamsters_users1_idx` (`users_id` ASC),
  UNIQUE INDEX `public_id_UNIQUE` (`public_id` ASC, `users_id` ASC),
  CONSTRAINT `fk_hamsters_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `dogs_BINS` BEFORE INSERT ON `dogs` FOR EACH ROW
INSERT INTO _inc (users_id,type,public_id) VALUES (NEW.users_id, "dogs", 1) ON DUPLICATE KEY UPDATE public_id=public_id+1;
SET NEW.public_id =
     (
        SELECT public_id FROM _inc WHERE users_id=NEW.users_id AND type="dogs";
     );$$

USE `mydb`$$
CREATE TRIGGER `cats_BINS` BEFORE INSERT ON `cats` FOR EACH ROW
INSERT INTO _inc (users_id,type,public_id) VALUES (NEW.users_id, "cats", 1) ON DUPLICATE KEY UPDATE public_id=public_id+1;
SET NEW.public_id =
     (
        SELECT public_id FROM _inc WHERE users_id=NEW.users_id AND type="cats";
     );$$

USE `mydb`$$
CREATE TRIGGER `hamsters_BINS` BEFORE INSERT ON `hamsters` FOR EACH ROW
INSERT INTO _inc (users_id,type,public_id) VALUES (NEW.users_id, "hamsters", 1) ON DUPLICATE KEY UPDATE public_id=public_id+1;
SET NEW.public_id =
     (
        SELECT public_id FROM _inc WHERE users_id=NEW.users_id AND type="hamsters";
     );$$


DELIMITER ;

[benanamen]

Why are you creating multiple tables? You should have ONE animals table linked to an animal_type_id in a animal type table. What is with the multiple keys?

[Barand]

Make the primary key (pet_type, pet_id) where the pet_id is auto_increment. (This increments it within the pet type) EG

mysql> CREATE TABLE `pet` (
    ->   `user_id` int(11) DEFAULT NULL,
    ->   `pet_type` varchar(45) NOT NULL,
    ->   `pet_id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`pet_type`,`pet_id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.34 sec)

mysql> INSERT INTO pet (user_id, pet_type) VALUES
    -> (1, 'cat'),
    -> (2, 'cat'),
    -> (2, 'dog'),
    -> (3, 'cat'),
    -> (4, 'dog');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pet;
+---------+----------+--------+
| user_id | pet_type | pet_id |
+---------+----------+--------+
|       1 | cat      |      1 |
|       2 | cat      |      2 |
|       2 | dog      |      1 |
|       3 | cat      |      3 |
|       4 | dog      |      2 |
+---------+----------+--------+

[NotionCommotion]

Why are you creating multiple tables? You should have ONE animals table linked to an animal_type_id in a animal type table. What is with the multiple keys?

 

Because different types of animals require different properties, however,  I agree they also share some common properties and as such a supertype/subtype solution is appropriate.

 

The "public_id" is provided to expose to the user, and is not joined to any other tables.

 

Do you know how I can accomplish this?

[benanamen]

Without the exact detail on the public_id I cannot make any comments on that part.

 

they also share some common properties and as such a supertype/subtype solution is appropriate.

 

You are on the right track here. Is this the part you do not understand how to do? On a side note, MongoDB is very well suited to this type of data.

Edited February 18, 2017 by benanamen

[NotionCommotion]

Make the primary key (pet_type, pet_id) where the pet_id is auto_increment. (This increments it within the pet type) EG

 

As addressed to banadamen's comment, different types of animals require different properties.  That being said, I suppose it is irreverent as I need the same solution even if there was just one table.

 

I am actually currently doing very similar to what you have shown.  I have a single MyISAM table with columns id, users_id, and type all making up the primary key, and have it autoincrementing id.  I insert a record, get the autoincrementing  id value, and then use the application to use that id along with the users_id in he dogs, cats, hamsters table.

 

I have two issues with this approach:

1. MyISAM doesn't support referential integrity constraints so I really should be using triggers to enforce instead of the application.
2. Composite PKs seem all good, but they really become a pain when you have several JOINs and every table needs to include the composite key.  I would like opinions on this as I am still on the fence, but starting side against them.
3. Not a big deal, but I need to store all the previous used public ids in this MyISAM table.  Guess I can easily enough add a trigger to delete the old ones when a new one is added.

 

Thanks

Edited February 18, 2017 by NotionCommotion

[NotionCommotion]

Without the exact detail on the public_id I cannot make any comments on that part.

 

 

You are on the right track here. Is this the part you do not understand how to do? On a side note, MongoDB is very well suited to this type of data.

 

I already understand how super/subtype tables work.  Thanks for the recommendation about MongoDB.

 

The public_id is just an incrementing non-reusable series of numbers starting from 1 and going up for each users' dogs, cats, and hamsters.  My only need is to figure out how to implement it.  How about if I didn't care about cats or hamsters (notice how the cat got deleted, but not the dog!), just had one "dogs" table which had id, public_id, users_id, and some various other columns. How can I implement this incrementing column under this scenario?

 

PS.  Think of the public_id as the person's SSN.  Yes, some will argue that it should be used as a natural primary key for the users table, but most would recommend using a surrogate and placing a unique constraint on SSNs.

Edited February 18, 2017 by NotionCommotion

[benanamen]

I don't have time at the moment to get into the implementation. I am sure Barand will jump in before I can. As far as MyISAM, what is stopping you from using InnoDB?

[Jacques1]

As far as MyISAM, what is stopping you from using InnoDB?

 

The fact that the proposed “solution” is yet another hack which only “works” with MyISAM.

 

We had the exact same discussion last year. Sacrifcing data integrity and simplicity just to have “pretty” IDs is nonsense. It didn't make sense then, it still doesn't make sense, and it won't make sense in the future.

Edited February 18, 2017 by Jacques1

[NotionCommotion]

I don't have time at the moment to get into the implementation. I am sure Barand will jump in before I can. As far as MyISAM, what is stopping you from using InnoDB?

 

InnoDB doesn't support incrementing composite keys.

[NotionCommotion]

The fact that the proposed “solution” is yet another hack which only “works” with MyISAM.

 

We had the exact same discussion last year. Sacrifcing data integrity and simplicity just to have “pretty” IDs is nonsense. It didn't make sense then, it still doesn't make sense, and it won't make sense in the future.

 

 

Ha, ha,  Yes, I remember those discussions (I was kind of hoping you didn't), and it is my time to eat crow.  The MySQL purists often preach the virtues of composite keys, and while they sometimes save a JOIN, and might sometimes make some application work simpler, they in my opinion make the application work more complicated and less flexible, and are not worth it.

 

So, anyone know how to implement this second incrementing key? I believe my originally proposed solution was going in the right direction, and would appreciate recommendations.

[benanamen]

@NotionCommotion, I think it's time for an intervention.

[NotionCommotion]

@NotionCommotion, I think it's time for an intervention.

 

???

 

I was under the impression that jaques1 scolding of insisting on pretty IDs was directed at me.

 

Or maybe "intervention" in a good way

[Jacques1]

Who said anything about a composite key? Use a plain old auto-incremented integer ID for your entire zoo. If you're worried about the counter leaking information, use purely random IDs.

 

This thread reminds me of the people who spend days trying to figure out how to fill gaps in numeric IDs and end up with hideously complex, bug-ridden locking gymnastics – so that they can have a “beautiful” sequence which nobody cares about.

 

The point of an ID is to identify data. That's it.

Edited February 19, 2017 by Jacques1

[benanamen]

This thread reminds me of the people who spend days trying to figure out how to fill gaps in numeric IDs and end up with hideously complex, bug-ridden locking gymnastics – so that they can have a “beautiful” sequence which nobody cares about.

 

LOL! While I never did anything about it, in my early days missing numbers really bothered me.

 

Notion, in a good way.

 

An intervention is the process by which an addict's family, friends, counselors or professional intervention specialists can show the addict his destructive behaviors in a way that may result in the addict choosing to seek help immediately.

 

We here need to gather to save you from yourself.   

Edited February 19, 2017 by benanamen

[NotionCommotion]

Who said anything about a composite key? Use a plain old auto-incremented integer ID for your entire zoo. If you're worried about the counter leaking information, use purely random IDs.

 

This thread reminds me of the people who spend days trying to figure out how to fill gaps in numeric IDs and end up with hideously complex, bug-ridden locking gymnastics – so that they can have a “beautiful” sequence which nobody cares about.

 

The point of an ID is to identify data. That's it.

 

Who?  I said something about a composite key as I've been told by some smart SQL people that it is a good (beautiful) thing.  I've since come to my senses, agree with your point of view, and want to use a plain old auto-incremented integer ID for my entire zoo.

 

The ID am am talking about is not for the database, but for the user.  Given that ID and their usersID (which they don't know, but they logged on so the system knows it), they can find a record which happens to have a plan old auto-incremented integer ID (which they they also will never know know as well).

 

We all have a user ID on this site (barand: 3105, benanamen: 179806, jacques1: 167590, and me: 26806).  There are also IDs to identify posts (303227 for this one).  One could argue based on a risk analysis whether these shouldn't be actual primary keys which the database uses, but it doesn't matter for what I am asking.

 

What if it was desired to instead of me having to refer to this post as #303227, I should refer to it as "NotionCommotion's post #1186"? Please assume that this is a UX requirement that management says needs to be implemented and that this second number should be a sequence and not a random value, and don't argue that these business rules shouldn't be implemented.

 

Well, we can change the schema and use a composite primary key (26806-1186 for this record), but as I said, I don't wish to use composite keys.  Instead I want the post table to be:

posts
-id (PK auto-incremented where no one sees its value)
-users_id (FK)
-my_own_public_id (unique index with users_id, will never be a FK to another table, and will be 1186 for this post)
-date_added
-content
-whatever

If I delete this post, and then add another one, I want my_own_public_id to be 1187, and not duplicate 1186 (please assume it the right thing for my particular application to delete a record instead of leaving it in the database and just flagging it as deleted).

 

So, how do I determine the value of this secondary extra key which is provided for the UX and not database integrity?

Edited February 19, 2017 by NotionCommotion

[NotionCommotion]

We here need to gather to save you from yourself.   

 

Well, that is sure reassuring and appreciated!

[NotionCommotion]

All I was asking was whether my implementation of triggers was correct to meet my stated objectives.  With the exception of Barand's first post (which unfortunately I don't wish to implement), all I got were opinions that my objectives were wrong.  For my specific application, I believe my objectives are appropriate and I never asked for my objectives to be validated.  I recognize that all advice received has been given freely and truly appreciate all you have all given whatever the format, but I do hope to implement this and hope someone can provide specific advice for the problem at hand. Thank you

[NotionCommotion]

It appears that my proposed solution in the original post might not be feasible as it results in the following error:

ERROR 1235 (42000) at line 68: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

It appears that I might need to utilize MyISAM to accomplish this.  Make _inc a primary composite incremented table, insert a value, get the last value inserted, and then use this for the other table.

Edited February 19, 2017 by NotionCommotion

[Jacques1]

Obtaining the ID from the _inc table with a simple INSERT and a session variable would be boring, right? You just need the thrill of finding creative ways to screw up your data.

[NotionCommotion]

Obtaining the ID from the _inc table with a simple INSERT and a session variable would be boring, right? You just need the thrill of finding creative ways to screw up your data.

 

Boring is good!  How can the session variable be used to return the incremented ID so it may be inserted in the intended table?  Or do you mean not using a trigger, but have PHP insert a record in _inc and returning that incremented ID to the PHP application, and having PHP insert this value in the intended table?

[NotionCommotion]

This is what I ended up doing, and it works great.  My original post was close (or maybe the same?), but I accidentally didn't delete the old trigger when adding new ones, thus the error message.

-- MySQL Script generated by MySQL Workbench
-- 02/21/17 21:26:39
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`users` ;

CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `userSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`_inc`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`_inc` ;

CREATE TABLE IF NOT EXISTS `mydb`.`_inc` (
  `type` CHAR( NOT NULL,
  `users_id` INT NOT NULL,
  `public_id` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`type`, `users_id`),
  INDEX `fk__inc_users_idx` (`users_id` ASC),
  CONSTRAINT `fk__inc_users`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`dogs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`dogs` ;

CREATE TABLE IF NOT EXISTS `mydb`.`dogs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `users_id` INT NOT NULL,
  `public_id` INT NOT NULL DEFAULT 0,
  `name` VARCHAR(45) NOT NULL,
  `dogSpecificStuff` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_dogs_users1_idx` (`users_id` ASC),
  CONSTRAINT `fk_dogs_users1`
    FOREIGN KEY (`users_id`)
    REFERENCES `mydb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$

USE `mydb`$$
DROP TRIGGER IF EXISTS `mydb`.`dogs_BINS` $$
USE `mydb`$$
CREATE TRIGGER `dogs_BINS` BEFORE INSERT ON `dogs` FOR EACH ROW
begin
INSERT INTO _inc (type, users_id, public_id) values ("dogs",NEW.users_id,1)
    ON DUPLICATE KEY UPDATE public_id = public_id + 1;
  SET NEW.public_id=(SELECT public_id FROM _inc WHERE users_id=NEW.users_id AND type="dogs");
end$$


DELIMITER ;