How to create Unique ID based on a pattern

Question

I am working on a student database project where i have to generate a unique 13-digit ID (within the database, not a universal unique ID) that is based on a pattern.

The Pattern

The pattern is like this: first four digits are a state code, next two digits are the year of admission of the student, and the remaining 7 digits are a simple sequence code e.g. 01031150000001.

The Problem

I cannot use the autoincrement feature of the mysql because the sequence numbers must be independent of state codes and year of admission.

To make it clear, consider i have only three state codes State A=0101, State B=0102 and State C=0103 and let us assume we are generating IDs for year 2015 so two digit year code would be 15.

Now the problem is that the first student admitted in state A should get the ID as 0101150000001, similarly the first student of state B should be assigned the ID 0102150000001 and the first student of state C should be assigned the ID 0103150000001 and these sequences should continue in this order independent of other states.

The problem is also complicated by the fact that for each year the sequence has to be restarted. For example, the first student for state A for year 2016 should get the ID 0101160000001. And to mess it up more, past student records are to be fed into the database at random, meaning that there may be a student whose date of admission is 2015 and such an entry might be followed by a student whose date of admission is 1998 and again it might be followed by a student whose date of admission is 2014 and so on.

What i am trying to say is that the data entry will not be sorted yearwise so there is no way to reset an autoincrement field every year.

The sequence for each of the states for every year has to be kept open or going for ever.

Although i can manage it with individual ID generating tables for each year for each state, but i was wondering if there is a more professional method of efficiently generating and managing such types IDs ????

Please do not mark it as a duplicate as i have already searched your forum as well as on google but could not find anything related. Thanks. By the way, i have to implement it in MySql and PHP.

The Structure

Currently, i need to store the data of a student in a table. There will be attributes like ID, first name, last name, address, parentage etc. There will be other tables containing data about the schools and other entities as well as the relationships between them. I have got everything in place. The problem is only that of the ID. I need a reliable ID generation algorithm for this purpose. The algorithm should never ever produce two similar IDs even if there are thousands of entries being made in the same second simultaneously.

Interesting post, what database structure do you have at the moment? I can think of a few ways but unless you post what you have so far no one will help — Neo, Oct 17 '15 at 19:23

jas- · Accepted Answer · 2015-10-17T20:25:31.090

The simplest solution would be a table design similar to the following.

First create a primary table to populate student records with; this only uses student_id but really would contain additional details such as names etc.

CREATE TABLE IF NOT EXISTS `students` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `student_id` BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTOINCREMENT=0;

Now you would have a one to one intermediary table containing fields for the state code, admission date and the unique id. By designing it in such a way you allow for a simple method implementing a recursive function which would handle a sequential value for the identifier field based on each referenced student_id record. Oh, I changed the year code to handle INT(4) (y2k bug much) & expanded the seven digit identifier to a BIGINT to handle growth.

CREATE TABLE IF NOT EXISTS `student_ids` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `student_id` BIGINT NOT NULL,
  `state` INT(4) NOT NULL,
  `year` INT(4) NOT NULL,
  `identifier` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_students2student_ids` FOREIGN KEY (`student_id`)
    REFERENCES `students`(`id`)
      ON UPDATE CASCADE
      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=0;

Now a recursive function to combine and determine the last numerical value for the identifier per combination of state + year + identifier.

<?php //pseudo-code, not tested
$states = array(0101, 0102, 0103);
$years = array(1999, 2000, 2001, 2002);

function robot_do_work() {
  foreach ($states as $st) {
    foreach ($years as $yr) {
      // Create PDO SQL statement
      $handle = $conn->pdo->prepare('SELECT SUM(MAX(`identfier`) + 1) FROM `student_id` WHERE `state` = :state AND `year` = :year DESC');

      // Execute PDO SQL statement with values for :state & :year
      $sth = $handle->execute(array(':state' => $st, ':year' => $yr));

      $record_incremented = $sth->fetchAll();
    }
  }
  return $record_incremented;
}
?>

Not my best work, but it should get you started with a solution

Thank you for your reply but i think there is a problem with this approach. My requirement is that each state alongwith each year should have its own continuous sequence. Your algorithm is great but it will create a common sequence shared among all states and years. But what is required is that each state should have its own independent continuous sequence for each year. — irshukhan, Oct 18 '15 at 13:52

How to create Unique ID based on a pattern

The Pattern

The Problem

The Structure

1 Answers1