11.18 Generating Repeating Sequences
11.18.1 Problem
You need to
create a sequence that contains cycles.
11.18.2 Solution
Generate a sequence and produce the cyclic elements using division
and the modulo operator.
11.18.3 Discussion
Some sequence-generation problems require values that go through
cycles. Suppose you're manufacturing items like
pharmaceutical products or automobile parts, and you must be able to
track them by lot number if manufacturing problems are discovered
later that require items sold within a particular lot to be recalled.
Suppose also that you pack and distribute items 12 units to a box and
6 boxes to a case. In this situation, item identifiers are three-part
values: The unit number (with a value from 1 to 12), the box number
(with a value from 1 to 6), and a lot number (with a value from 1 to
whatever the highest case number happens to be currently).
This item-tracking problem appears to
require that you maintain three counters, so you might think about
generating the next identifier value using an algorithm like this:
retrieve most recently used case, box, and unit numbers
unit = unit + 1 # increment unit number
if (unit > 12) # need to start a new box?
{
unit = 1 # go to first unit of next box
box = box + 1
}
if (box > 6) # need to start a new case?
{
box = 1 # go to first box of next case
case = case + 1
}
store new case, box, and unit numbers
You could indeed implement an algorithm that way. On the other hand,
it's also possible simply to assign each item a
sequence number identifier and derive the corresponding case, box,
and unit numbers from it. The identifier can come from an
AUTO_INCREMENT column or a
single-row sequence generator. The
formulas for determining the case, box, and unit numbers for any item
from its sequence number look like this:
unit = ((seq - 1) % 12) + 1
box = (int ((seq - 1) / 12) % 6) + 1
case = int ((seq - 1)/(6 * 12)) + 1
The table shown below illustrates the relationship between some
sample sequence numbers and the corresponding case, box, and unit
numbers:
seq case box unit
1 1 1 1
12 1 1 12
13 1 2 1
72 1 6 12
73 2 1 1
144 2 6 12
|
