Math operations on products alpha-num serial numbers | Database Design

So in my database, I got the tables

Product (prodId,Name,Price)
Box (BoxId,prodId,From,To,Available)

'From' represents the first serial number. And 'To' the ending serial. Calculating 'To' sub 'From' gives the quantity of products.

A client comes and makes an order of a given product with a given quantity. What I need ,is given the 'From' serial number,I calculate 'From' + Quantity. If the serial numbers were only sequential integers. This would be easy. But this applies to all types of products with different serial numbers. For ex :

Box( 1,1,ABC00000C,ABC00099K,100)

What I want to achieve is this :

SELECT From + 50 FROM BOX

How Can i deal with the serial number to get the order ending serial ?

1 answer

  • answered 2017-10-11 10:14 rrozema

    To deal with such serial numbers you need either (option 1) a fn(x) calculating a serial number given an integer x, or (option 2) a list of available serial numbers.

    Option 1 is easiest to implement, but it requires that the person thinking up the serial number format actually did think of making up a conversion formula to convert an integer into a serial number also. If such a formula exists, all you need to do is determine the integer value for the "from"-value, add 50 to this integer value 'x' and determine the serial number for 'x + 50'.

    Option 2 requires that you have a list, or can generate a list of serial numbers, plus those serial numbers must be (somehow) logically ordered. Option 2 then applies one of many ways SQL server provides to get the next 50 rows from this list, starting from the row with value "From" in it. Examples of such methodes are "select top (50) ...", window function "row_number() over (order by ...)", "select ... order by ... offset n rows fetch next 50 rows only" or even a cursor.

    Added after comment from Wildfire:

    I suggest you create a table holding the serials for option 2. Let me explain this by giving an example: what would you do if one item with serial n + 5 happens to have fallen of the production line and was damaged beyond repair? I.e. this one serial number will never be shipped to a customer. I bet you are not going to ship a box with one less item when this happens, nor are you going to discard 49 undamaged products because the one item is missing. Instead you will probably put all products with serials n to n + 4 and n + 6 to n + 51 in a box, leaving serial n + 5 out. In a perfect world this will of course never happen, but in real life things do go wrong sometimes, so you need to able to cope with -for example- missing serials. So I would really suggest creating a table with all serials available for boxing, and simply have your boxing process read it's next 50 serials from this table.

    And option 1 can work, even if the serial itself is non-numerical but can be calculated into a numerical. It's just a little more complicated. That's why I said a formula must exist for the serials for the method to work. Here's an example how you could add 50 to serial "ABC00000C", making "ABC00001Y" the to serial:

    declare @from varchar(9) = 'ABC00000C';
    
    declare @from_int bigint;
    
    with cteSerialCharacters as (
        -- The set of characters used in a serial.
        -- As an example I've taken all number characters plus
        -- all capital letters from the alphabet excluding any 
        -- of these that are easily misread.
        select '0123456789ABCDEFGHJKLMNPRSTVWXYZ' as chars
    ),
    cteNumberGenerator as (
        select cast (row_number() over (order by (select null)) as bigint) as n
        from ( select 1 union all select 1 union all select 1 union all select 1
                union all select 1 union all select 1 union all select 1 
                union all select 1 union all select 1
            ) t (xyz)
    )
    select 
        @from_int = sum(power(s.base, (n - 1)) * (-1 + charindex(substring(reverse(s.serial), n.n, 1), s.characterset)))
    from (
            select 
                @from,
                cast(len(ch.chars) as bigint),
                ch.chars
            from cteSerialCharacters ch
        ) s (serial, base, characterset)
        inner join cteNumberGenerator n on (n.n <= len(s.serial));
    
    
    select @from, @from_int;
    
    declare @to varchar(9);
    declare @to_int bigint;
    
    
    select @to_int = @from_int + 50;
    
    
    with cteSerialCharacters as (
        -- The set of characters used in a serial.
        -- As an example I've taken all number characters plus
        -- all capital letters from the alphabet excluding any 
        -- of these that are easily misread.
        select '0123456789ABCDEFGHJKLMNPRSTVWXYZ' as chars
    ),
    cteNumberGenerator as (
        select cast (row_number() over (order by (select null)) as bigint) as n
        from ( select 1 union all select 1 union all select 1 union all select 1
                union all select 1 union all select 1 union all select 1 
                union all select 1 union all select 1
            ) t (xyz)
    )
    select 
        @to = (
                select 
                    substring(s.characterset, 1 + (@to_int / power(s.base, n.n - 1)) % s.base, 1) as [text()]
                from (
                        select 
                            cast(len(ch.chars) as bigint),
                            ch.chars
                        from cteSerialCharacters ch
                    ) s (base, characterset)
                    cross join cteNumberGenerator n
                order by n.n desc
                for xml path(''), type
            ).value('text()[1]', 'varchar(9)')
    
    select @to, @to_int;