Ensure sequence numbers of child records start at 1.

Question

This is a hard one (I think), on a SQL Server 2008 R2 database. I have a NotificationTemplate with zero to many NotificationSmsTemplate child bjects, because our SMS limits messages to 160 characters, I need to send several SMS's in sequence to deliver the whole SMS communication. Per NotificationTemplate, each NotificationSmsTemplate has an SmsSequenceNumber property. This number can be anything as long as it is unique to it's containing NotificationTemplate.

How can I ensure that the sequence numbers start 1 1 and are contiguous fro the collection of NotificationSmsTemplate objects for a containing NotificationTemplate. I can only think of using an internal sequence number to allow the user to order templates on teh screen, and then ordering by that, generate visible sequence numbers while inserting into the DB.

Answer 1

If I understand your requirements correctly, you can take advantage of the way MySQL will reuse AUTO_INCREMENT values when the AUTO_INCREMENT column is part of a multiple-column index in a MyISAM table. See Using AUTO_INCREMENT for more details.

Here's an example to illustrate what I mean:

Create a table to hold your notification templates:

CREATE  TABLE IF NOT EXISTS `notification_template` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `notification` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;

Create a table to hold the SMS templates:

CREATE  TABLE IF NOT EXISTS `notification_sms_template` (
  `notification_template_id` INT NOT NULL ,
  `sms_sequence_number` INT NOT NULL AUTO_INCREMENT ,
  `sms` VARCHAR(160) NOT NULL ,
  PRIMARY KEY (`notification_template_id`, `sms_sequence_number`) )
ENGINE = MyISAM;

Insert a couple of long text passages into the notification_template table:

INSERT INTO `notification_template` (`id`, `notification`) VALUES
(1, 'Tell me, O Muse, of that ingenious hero who travelled far and wide after he had sacked the famous town of Troy. Many cities did he visit, and many were the nations with whose manners and customs he was acquainted; moreover he suffered much by sea while trying to save his own life and bring his men safely home; but do what he might he could not save his men, for they perished through their own sheer folly in eating the cattle of the Sun-god Hyperion; so the god prevented them from ever reaching home. Tell me, too, about all these things, oh daughter of Jove, from whatsoever source you may know them.'),
(2, 'There were once a man and a woman who had long in vain wished for a child. At length the woman hoped that God was about to grant her desire. These people had a little window at the back of their house from which a splendid garden could be seen, which was full of the most beautiful flowers and herbs. It was, however, surrounded by a high wall, and no one dared to go into it because it belonged to an enchantress, who had great power and was dreaded by all the world.');

Insert the 160 character chunks into the notification_sms_template table, specifying the id of the notification_template to which they relate:

INSERT INTO `notification_sms_template` (`notification_template_id`, `sms`) VALUES
(1, 'Tell me, O Muse, of that ingenious hero who travelled far and wide after he had sacked the famous town of Troy. Many cities did he visit, and many were the nat'),
(1, 'ions with whose manners and customs he was acquainted; moreover he suffered much by sea while trying to save his own life and bring his men safely home; but do'),
(1, ' what he might he could not save his men, for they perished through their own sheer folly in eating the cattle of the Sun-god Hyperion; so the god prevented th'),
(1, 'em from ever reaching home. Tell me, too, about all these things, oh daughter of Jove, from whatsoever source you may know them.'),
(2, 'There were once a man and a woman who had long in vain wished for a child. At length the woman hoped that God was about to grant her desire. These people had a'),
(2, ' little window at the back of their house from which a splendid garden could be seen, which was full of the most beautiful flowers and herbs. It was, however, '),
(2, 'surrounded by a high wall, and no one dared to go into it because it belonged to an enchantress, who had great power and was dreaded by all the world.');

If you now select the the IDs from notification_sms_template, you will see that sms_sequence_number starts at 1 for each notification_template:

SELECT `notification_template_id`, `sms_sequence_number`
FROM `notification_sms_template`;

+--------------------------+---------------------+
| notification_template_id | sms_sequence_number |
+--------------------------+---------------------+
|                        1 |                   1 |
|                        1 |                   2 |
|                        1 |                   3 |
|                        1 |                   4 |
|                        2 |                   1 |
|                        2 |                   2 |
|                        2 |                   3 |
+--------------------------+---------------------+