• (cs)

    Ah, the a-database-needs-only-one-table philosophy. Good times.

  • drx (unregistered)

    I bet redesigning the entire database would take less time then trying to figure out how to query that table and group the data into something reasonable.

  • Dr DBA (unregistered)

    Do you KNOW how much it costs to create a table in SQL Server? The "polymorphic-relational" pattern is an effective way to mitigate the need to create expensive new tables, and has proven itself over the years. This is not by any means a WTF.

    However, I think it is now generally acknowledged that storing all data within an XML fragment in a single text field is a superior approach, as it also minimizes the unnecessary creation of columns, and is highly extensible. Often one only needs a single table with a single column for even the most complex of applications! I like to call this the "flexml" pattern, as it's just so gosh-darn flexible.

  • Stephen (unregistered)

    "that's right, you haven't seed that module yet; you better sit down for this one."

    Ed: Fixed! Need to stop writing these up during Lost...

  • Tony (unregistered)

    Un-bLevelable

  • (cs)

    Man, reminds me of a post last week on databases that were not normalized. This is even worse. In both cases, using a database system did hardly anything for them. Sounds like most of the development team there is the problem, a little organization and structure would simplify things, make them easier to understand, and almost eliminate those "you'll want to sit down for this" discussions. If you structure things well, you make it easier for everyone to understand better.

  • Enterprisey Architect (unregistered) in reply to Dr DBA

    I think that using a BLOB to store the XML is still far superior to the single text field.

  • tego (unregistered)

    Let me guess, all l were ints all b were varchar(5) (for "true" or "false")

    and the rest were text?

  • Just Another Bloke (unregistered) in reply to Enterprisey Architect
    Enterprisey Architect:
    I think that using a BLOB to store the XML is still far superior to the single text field.

    You do mean using a single record with one BLOB column in which a zip-file is stored containing the XMLs, of course.

    Then there's one column, one row: can't beat that, right?

  • (cs)

    Disburbing?

  • Dr DBA (unregistered) in reply to Enterprisey Architect
    Enterprisey Architect:
    I think that using a BLOB to store the XML is still far superior to the single text field.

    A BLOB?! Excuse my language, but what the French toast?

    I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

    Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

    To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

    <flexml>
     <key>Person</key>
     <value>Dr DBA</value>
     <key>PersonHasShippingAddress</key>
     <value>35,138</value>
     <key>ShippingAddress35</key>
     <value>1 Main St, Anytown</value>
     <key>ShippingAddress138</key>
     <value>35 36th St, New York, NY</value>
</flexml>
  • Shinobu (unregistered)

    I really miss that girl who was capable of performing Ireland.

  • me (unregistered)

    Don't be so harsh, guys, the original developer deserves bonus points for spelling "defenition" right.

  • KT (unregistered) in reply to tego
    tego:
    Let me guess, all l were ints all b were varchar(5) (for "true" or "false")

    and the rest were text?

    You forgot FILE_NOT_FOUND and NotQuiteTrue it at least need to be VARCHAR(20)

  • (cs) in reply to Dr DBA
    Dr DBA:
    I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

    Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

    I thought it was funny that you were laughing at your own jokes. Not that they aren't funny, but you're probably laughing harder than us :)

  • Najiib (unregistered) in reply to KT

    I come to this site for the wonderful programming ideas and techniques. I'm not familiar with using xml so instead, I will store an MS-Access database in an Oracle blob in a one column one row table. For efficiency, I will keep it zipped.

  • Anomynous (unregistered)

    psssht, obviously he just needs to join on dtUserDefined3

  • (cs)

    My favorites are the columns obviously reserved for the designer of this table:

    lUserDefined1 lUserDefined2 lUserDefined3
  • Trawn (unregistered)

    This isn't hard. While you couldn't search do a function search by Name, you could use the Great Circle function to find objects near a location using a range. Join against a static hash table of Location Name, Lat, Long.

    Zipcode databases are at most $100. Free if you can rip them off from the Census.

    I imagine it became a WTF because probably the agents have service locations. For example a traveling service guy could work one day in NY, the next in Chicago, the next in LA. The database may have been designed appropriately for that scenario.

  • conservajerk (unregistered) in reply to Enterprisey Architect
    Enterprisey Architect:
    I think that using a BLOB to store the XML is still far superior to the single text field.

    No way, microsoft is waaaay ahead of you. Use the xml data type in sql server 2005.

    So did this dude really quit once he saw the table schema? I once waded through a multi-hundred line function which repeatedly reused local variables ("for efficiency") and it took me a good 8 months to leave. You people are just too wussy!

  • [twisti] (unregistered) in reply to KenW
    KenW:
    My favorites are the columns obviously reserved for the designer of this table:
    lUserDefined1 lUserDefined2 lUserDefined3

    That's so hilarious. I've never seen someone make that joke.

  • (cs)

    Wait... there's already a Location class? Well, then...

    LocationEntity location = new LocationEntity(targetLocation);
    Voila! Problem solved.

  • SomeCoder (unregistered)

    "David always dreading having to ask his coworkers about the system. No matter what the question was, no matter how easy it seemed, they always responded in the same manner: smacking their hand to their forehead, sighing, and then saying in an apologetic tone: "that's right, you haven't seen that module yet; you better sit down for this one." Rarely did their explanation disappoint."

    That sounds like my job. You'd think getting some simple information out of the database would be easy but every time I have to ask, I get that response and the explanation usually takes at least 2 hours.

  • (cs)

    oh.

    my.

    goodness.

    I have tears welling up in my eyes right now. And I haven't even been maced.

  • Adam G (unregistered)

    Holy denormalized table, Batman!

  • (cs)

    Ah--Pretty Simple after all--all you have to know is which lCapabilityTypeID values are locations, then search for matches on sName to GetLocationByName, and also knowing what values for bActive and bSelectable to use to help filter the search correctly, and how to make use of (or even if it's necessary to use) the fields lParentID, lOldParentID, lTreeID, lRightID, lOldTreeID, lSelectableTreeParentID, lOldSelectableTreeParentID, lIndentLevel, bLevel1, bLevel2 to make sure you're getting at the right data and not some incorrect branch or version of the data, and of course you have to make sure to filter on all the other fields appropriately to get down to the correct information.

    Or maybe it's as simple as knowing who the president of the country is as filtering where sCustodianName is the president of the country you want?

  • fatass (unregistered)

    is that Onyx?

  • Moose (unregistered)

    WHAT THE *#$%!!! SERIOUSLY!!! WTF???!!! Is this some sadistic joke??!!!

  • LEGO (unregistered) in reply to Dr DBA
    Dr DBA:
    Enterprisey Architect:
    I think that using a BLOB to store the XML is still far superior to the single text field.

    A BLOB?! Excuse my language, but what the French toast?

    I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

    Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

    To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

    <flexml>
     <key>Person</key>
     <value>Dr DBA</value>
     <key>PersonHasShippingAddress</key>
     <value>35,138</value>
     <key>ShippingAddress35</key>
     <value>1 Main St, Anytown</value>
     <key>ShippingAddress138</key>
     <value>35 36th St, New York, NY</value>
</flexml>

    You guys still are not getting it.

    You want to store these things as pictures of xml fragments taken on a wooden table, UUencoded and placed into memo fields in an MSAccess database, zipped, tarred, and stored in a single column of a single table in an oracle database.

    -LEGO

  • dkf (unregistered) in reply to Dr DBA
    Dr DBA:
    Do you KNOW how much it costs to create a table in SQL Server?
    No, and for that I thank my lucky stars.
  • (cs) in reply to dkf
    dkf:
    Dr DBA:
    Do you KNOW how much it costs to create a table in SQL Server?
    No, and for that I thank my lucky stars.

    We're paid to do work, not buy work. Let the other people worry about the costs.

  • Dude (unregistered)

    something like this should be even funner to do in SQL Server 2008 when they add the spatial data type

  • Dr DBA (unregistered) in reply to pitchingchris
    pitchingchris:
    Dr DBA:
    I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

    Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

    I thought it was funny that you were laughing at your own jokes. Not that they aren't funny, but you're probably laughing harder than us :)

    "Lol!"

  • silent d (unregistered) in reply to dpm

    one table to rule them all...

  • silent d (unregistered) in reply to It's a Feature
    It's a Feature:
    Ah--Pretty Simple after all--all you have to know is which lCapabilityTypeID values are locations, then search for matches on sName to GetLocationByName, and also knowing what values for bActive and bSelectable to use to help filter the search correctly, and how to make use of (or even if it's necessary to use) the fields lParentID, lOldParentID, lTreeID, lRightID, lOldTreeID, lSelectableTreeParentID, lOldSelectableTreeParentID, lIndentLevel, bLevel1, bLevel2 to make sure you're getting at the right data and not some incorrect branch or version of the data, and of course you have to make sure to filter on all the other fields appropriately to get down to the correct information.

    You should be fine as long as you include lRightID and not lWrongID.

  • (cs) in reply to LEGO
    LEGO:
    Dr DBA:
    Enterprisey Architect:
    I think that using a BLOB to store the XML is still far superior to the single text field.

    A BLOB?! Excuse my language, but what the French toast?

    I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

    Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

    To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

    <flexml>
     <key>Person</key>
     <value>Dr DBA</value>
     <key>PersonHasShippingAddress</key>
     <value>35,138</value>
     <key>ShippingAddress35</key>
     <value>1 Main St, Anytown</value>
     <key>ShippingAddress138</key>
     <value>35 36th St, New York, NY</value>
</flexml>

    You guys still are not getting it.

    You want to store these things as pictures of xml fragments taken on a wooden table, UUencoded and placed into memo fields in an MSAccess database, zipped, tarred, and stored in a single column of a single table in an oracle database.

    -LEGO

    Don't forget to store it on a zip-drive for maximum moving efficiency.

    I should be a BA.

  • (cs) in reply to silent d
    silent d:
    one table to rule them all...

    One sproc to find them.

  • (cs) in reply to Pope
    Pope:
    silent d:
    one table to rule them all...

    One sproc to find them.

    One trigger to bring them all..

  • iMalc (unregistered)

    Man, you should have seen the six increasing levels of horror on my face as I scrolled down eight lines at a time at the end of that article!

    Oh crap that means there's about 50 columns! ... Happy place, happy place ...

  • cod3_complete (unregistered)

    This is nothing! I've tamed tables with 144 columns and sprocs that took a corresponding 144 arguments. You guys are complete wimps! True WTF code I see it everyday. :-(

  • (cs) in reply to cod3_complete
    cod3_complete:
    True WTF code I see it everyday. :-(

    Good Job ! I just hope your eyes make it.

  • umm... (unregistered)

    Glanced ahead and instinctively interpreted lLongitude as ILongitude, i.e.:

    public interface class ILongitude
{
    virtual property float Longitude
    {
       float get(); 
       void set(float value);
    }
}
    Still haven't decided if that would've been worse than the actual wtf presented or not.
  • Ravn (unregistered)

    Obviously, the table was created in a fixed width terminal. The fields were never meant to be called lSomething but ISomething.

  • (cs) in reply to pitchingchris
    pitchingchris:
    Pope:
    silent d:
    one table to rule them all...

    One sproc to find them.

    One trigger to bring them all..

    And in the object bind them.

  • Steve G (unregistered) in reply to RayMarron
    RayMarron:
    pitchingchris:
    Pope:
    silent d:
    one table to rule them all...

    One sproc to find them.

    One trigger to bring them all..

    And in the object bind them.

    In the SQL Server, where the filegroups lie.

  • (cs) in reply to RayMarron
    RayMarron:
    pitchingchris:
    Pope:
    silent d:
    one table to rule them all...

    One sproc to find them.

    One trigger to bring them all..

    And in the object bind them.
    Datagrid, son. In the datagrid bind them.

  • Anonymous Coward (unregistered) in reply to FredSaw
    FredSaw:
    RayMarron:
    pitchingchris:
    Pope:
    silent d:
    one table to rule them all...

    One sproc to find them.

    One trigger to bring them all..

    And in the object bind them.
    Datagrid, son. In the datagrid bind them.

    Someone, somewhere hasn't been taking their 'I Hate Oracle' medication.

    You have all forgotten.... cursors.

  • anm (unregistered) in reply to KenW

    I think those are actually in use, but the content depends on

    • the content of the first field
    • the id is odd
    • the actual season is spring [more, even more stupid conditions]
    KenW:
    My favorites are the columns obviously reserved for the designer of this table:
    lUserDefined1 lUserDefined2 lUserDefined3
  • (cs) in reply to Anonymous Coward
    Anonymous Coward:
    Someone, somewhere hasn't been taking their 'I Hate Oracle' medication.

    You have all forgotten.... cursors.

    Cursors! Foiled again!

  • (cs)

    This is something that I saw every day. Not this specific table, but the design. It's just absurd how many enterprise databases can be so heavily used, yet be so mismanaged and so poorly conceived that it's sick. I've seen tables that have had literally dozens of "UserDefinedX" columns and the like that I honestly thought that it was some sort of joke -- until I saw how many records was in that table and how heavily used it was.

    It may be a WTF in the sense that it is poorly designed and makes no sense, but it is not a WTF in the sense that this hasn't been seen or isn't being done in multiple enterprise environments every day.

Leave a comment on “Not So Simple”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article