Database WTFs



  • We host data for our clients, and a lot of our databases were not put together by our company. Sometimes you can tell whoever built the database had no idea what they were doing, though part of the problem has to do with the software package used to build these databases which enforces some database concepts (such as domain constraints or even foreign keys) in code instead of in the database. As stuff changes between versions or people edit stuff through SQL Server Management Studio instead of the software package itself, you end up with data that wouldn't be possible in a properly-designed database.

    Here is some of the stuff I've encountered while doing QC (datatypes are listed in their .NET-equivalent):

    • Year values stored as doubles
    • Duplicate columns containing the same data in different data types (for example, one table had two square-footage columns, one was a string and the other was a double)
    • Dates stored as strings
    • Maximum building occupancy stored as a string
    • Too many other examples of mismatched datatypes to mention
    • Domain-constrained columns with bad data (a column which is constrained to contain one of about a half-dozen values, yet someone somewhere was able to enter values outside of the domain)
    • Inconsistent way of representing "no value." Sometimes it's NULL, sometimes it's an empty string, sometimes it's a single space, sometimes two spaces, sometimes "N/A", all in the same column in the same table.

    And the biggest WTF came from one that stored some historical items for a period of over twenty years. But rather than have a single table with a year field, there are a bunch of tables with identical schemas, one per year.

    Another big WTF I'm having to deal with isn't in the database. Part of my QC process involves testing configuration files for our clients that are being upgraded from version 1.0 to version 2.0 of our product. The 2.0 format of our config file is much, much simpler and cleaner than 1.0. I had an intern convert all of the config files from the 1.0 to the 2.0 format, which I figured would be easy for him since he helped write the parser for the 2.0 config file. Now that I'm QC'ing them all it seems he for the most part just copied the text out of the 1.0 file and pasted it into the 2.0 file, which of course doesn't work and now I'm having to manually go through and fix all of these files (he's no longer with us for other reasons).

    At least I'm getting a break from coding.



  • The product I'm, um, joyfully employed as the administrator for belongs in a wtf category of its own.

    To start with, the "integrated ticketing system" uses a single 110-column table for two vastly different ticket schemas (meaning 1/2 - 3/4 of the fields are empty for any row), and instead of using relational concepts in this table, all of the data is shoved into the table by the front end. So instead of having columns that relate back to the user and group tables, the front end inserts the text description of the user "username (User Name)" into columns. Instead of having a table of ticket number/assigned to person/group/datetime, I get a semicolon-separated field of datetime stamps (110123 03:45; 110123 04:25) and a semicolon-separated field of assignedto users (in the "username (User Name); otheruser (Other User)" format).

    We just closed one of the huge security holes in this thing, in that we require a login when looking at a ticket link sent via email. We've notified the vendor of another hole wherein you can change the url to show that you're someone else (which can lead to audit trail issues).

    IIS + AJAX + random toolkit that they're using + random DB decisions that don't look like a DBA was even questioned = tweek not sleeping well.
    The prospect of opening it up to the big I = total nightmare fuel.



  • last week I was asked  by another department to look at one of their triggers.  It was using 3 level nested cursors to insert data into auditing tables....

    I once ran across a rather "clever" implementation of bit columns.  The original developer had decided to combine the values of several checkboxes on a form into a single integer value, using bit logic, and store that in the database....

    A previous developer was "famous" for using nested cursors and query hints, especially (nolock), everywhere.  Especially in places where neither was needed nor a good idea.

    I spend my days doing performance tuning queries and sql server instances.

     



  • @galgorah said:

    I once ran across a rather "clever" implementation of bit columns.  The original developer had decided to combine the values of several checkboxes on a form into a single integer value, using bit logic, and store that in the database....

    I can beat that. As referenced [url=http://forums.thedailywtf.com/forums/t/24745.aspx]here[/url], an Oracle product with which I am blessed to work associates a unique prime number with each defined attribute, and identifies which attributes are selected for use on a given record by storing the product of the associated prime numbers. A DIY bitfield would be luxury by comparison, and probably wouldn't suffer the same sort of overflow issues.



  • The company I'm currently working for used to have custom datatypes for every column in their database tables. One of these types is defined as a tinyint, but is used as a bit and hence has a constraint to prevent any value apart from 0 or 1 being inserted.



  • @The_Assimilator said:

    One of these types is defined as a tinyint, but is used as a bit and hence has a constraint to prevent any value apart from 0 or 1 being inserted.

    That's handy if they ever need to add FILE_NOT_FOUND.



  • @Scarlet Manuka said:

    @galgorah said:
    I once ran across a rather "clever" implementation of bit columns.  The original developer had decided to combine the values of several checkboxes on a form into a single integer value, using bit logic, and store that in the database....
    I can beat that. As referenced here, an Oracle product with which I am blessed to work associates a unique prime number with each defined attribute, and identifies which attributes are selected for use on a given record by storing the product of the associated prime numbers. A DIY bitfield would be luxury by comparison, and probably wouldn't suffer the same sort of overflow issues.
    I remember that thread.  You have my condolences.

     It wasn't so much a makeshift bitfield as it was a makeshift bitarray.  You might have 32 checkbox values being stored in the same column.  <insert afterlife here> help you if a web developer changed the check box list.  say add a few new ones and remove/replace and another one.  Data integrity goes right out the window. 



  • @galgorah said:

     It wasn't so much a makeshift bitfield as it was a makeshift bitarray.  You might have 32 checkbox values being stored in the same column.  <insert afterlife here> help you if a web developer changed the check box list.  say add a few new ones and remove/replace and another one.  Data integrity goes right out the window. 

     

     Sounds like the options in Angband... "No, we can't take out that option, because if we do, then all the others get shoved up one slot and then when you load a saved game from the old version, your options get screwed up... well, OK, we can at least dummy out that option in the UI so it's not selectable anymore... what's that, you want to add a 33rd option? No way! There's not enough bitflags for that!"

     Also, if you set a trigger on a DATE field, does that make it a chrono trigger? :D

     



  • @ekolis said:

    Also, if you set a trigger on a DATE field, does that make it a chron

    NO

    (This post brought to you by The Committee To Prevent Awful Puns)



  • @ekolis said:

    Also, if you set a trigger on a DATE field, does that make it a chrono trigger? :D

    Only if you also have a column of type SpaceTime.  Keep in mind this field should NEVER be set to NULL.



  • @Scarlet Manuka said:

    @galgorah said:
    I once ran across a rather "clever" implementation of bit columns.  The original developer had decided to combine the values of several checkboxes on a form into a single integer value, using bit logic, and store that in the database....
    I can beat that. As referenced here, an Oracle product with which I am blessed to work associates a unique prime number with each defined attribute, and identifies which attributes are selected for use on a given record by storing the product of the associated prime numbers. A DIY bitfield would be luxury by comparison, and probably wouldn't suffer the same sort of overflow issues.
     

     

     

    My god, one of my co-workers was showing off just this trick to me the other day.


  • Discourse touched me in a no-no place

    @ekolis said:

    @galgorah said:

     It wasn't so much a makeshift bitfield as it was a makeshift bitarray.  You might have 32 checkbox values being stored in the same column.  <insert afterlife here> help you if a web developer changed the check box list.  say add a few new ones and remove/replace and another one.  Data integrity goes right out the window. 

     

     Sounds like the options in Angband... "No, we can't take out that option, because if we do, then all the others get shoved up one slot and then when you load a saved game from the old version, your options get screwed up... well, OK, we can at least dummy out that option in the UI so it's not selectable anymore... what's that, you want to add a 33rd option? No way! There's not enough bitflags for that!"

     Also, if you set a trigger on a DATE field, does that make it a chrono trigger? :D

     

    That Angband issue is sort of the opposite of what is just about the only NON-WTF in Pocket Humanity, a .Net compact framework game for (old versions of) Windows Mobile. The save file was versioned, and had code to deal with multiple versions, autoconverting them to whatever the current version of the game was.



  • @blakeyrat said:

    @ekolis said:
    Also, if you set a trigger on a DATE field, does that make it a chron

    NO

    ¡CHRONOLOCO! Dances the Mexican Hat Dance


Log in to reply