Defeating the purpose of an RDBMS



  • For a task at work I'm trying to check the past pricing on some of our products, as they were when an order was placed (they change fairly frequently).  In digging around trying to find the details, I came across this lovely gem:

    In the invoices table, the previous developer decided on a unique way to store details.  Now personally, I would use a separate table and store the quantity, sku, price, etc.  Instead, his method was to store a comma-separated list, separated by pipes (|) as a text column in the database, and parse it out in server-side VBScript like so:

    rdtls = split(rstemp("details"),"|")
    c = ubound(rdtls)
    for each elem in rdtls
    idtls = split(elem,",")
    ctr = 1
    for each itm in idtls
    select case ctr
    case 1
    qty = itm
    case 2
    sku = itm
    case 3
    lttl = itm
    case 4
    vttl = itm
    case else
    end select
    ctr = ctr + 1

    (yes, that's actually how the code is written in the page - that is copied and pasted verbatim).

    So what we have is a pipe-delmited list of comma-delimited lists, stored in a database text column, containing order details, that needs to be interated over in code to parse into proper format, and which therefore makes it nearly impossible to track down specific details. Seriously, WTF??



  •  Holy crap!  Two pipe-delimited RDMS field WTFs in a matter of minutes! 



  • If it's MySQL, use find_in_set()  http://www.bitbybit.dk/carsten/blog/?p=162

    PATINDEX would work on MS SQL Server.

    WTFy in a lot of situations, but surprising common in a lot of apps.



  • This showed up in one of our joint projects, though at least in that case there are only 2 columns involved.  Yes, I will insist it be rewritten properly in the next version (whenever that happens).

     



  • @morbiuswilters said:

     Holy crap!  Two pipe-delimited RDMS field WTFs in a matter of minutes! 

    I see the pipe trick used pretty often. I've actually used it in a very few cases myself. Not always a pipe as the delimiter. I personally only ever consider that if I'm dealing with something I'd never do any sort of individual search on (or such searches are rare, RARE maintenance tasks and a LIKE search isn't too expensive) ... And as such, off the top of my head, I can think of only two cases I've done that in an SQL database. There might be one or two more.

    I used to work with one guy in particular who was of the opinion that "any database that had fields that held only one thing is a bad database" (and he wrote monthly lectures to us about this point) who also started getting into OO-- the wrong way. He invented "polymorphic delimiters", as he called them...

    Polymorphic delimiters, in a nutshell, are to use the 'split' operator with a regular expression that contains the set of legal characters in the data, negated. He actually was indeed smart enough to validate (though not with constraints, of course) new data going in. Unfortunately, existing data was a problem, for two reasons...

    Combined numbers tended to blow up, because his regex for those included the phrase '1-9' instead of '0-9', and his regex for strings like customer names tended to blow up because as we all know, in idiot-land, W is the last letter of the database, not Z. I might have forgiven this as a typo had we not actually had to prove it to him with two dictionaries. Two, because he insisted the first was just wrong... When we had him write out the alphabet, he got it correct up to the end: Z Y X W.

    Ever since then, his nickname at the office was "Mismatched Endian Alphabet Man."



  • @Wolftaur said:

    When we had him write out the alphabet, he got it correct up to the end: Z Y X W.

     

    <font size="10">!</font>



  • @tster said:

    @Wolftaur said:

    When we had him write out the alphabet, he got it correct up to the end: Z Y X W.

     

    <font size="10">!</font>

    1. I saw that post between when you first made it and when you edited the font size. End result: When I saw it again, I dissolved with laughter.

    2. I should add a note about this guy: He used to brag about all of his stupid "miraculous inventions", the polymorphic delimiter being only one example. Management was firmly convinced that the flaws in implementation didn't undermine the brilliance of his ideas. So he got the first DEC Alpha workstation the company bought. I controlled network naming, and so the machine was named... alpha-stib.


Log in to reply