• Juan (unregistered)

    I had a near-death experience reading this. Great WTF! :)

  • frosty (unregistered)

    Didn't we have one similar to this a ways back (as in, within the last year or so)?

    It was where they created a table for everything that should be a "row" so that they wouldn't be constrained by a change in the DB schema.

    Either way, I'm not complaining.  You could have one of these twice a year and I'd be happy because it'd be keeping this design off the streets... maybe.

  • (cs)

    So, did Michael's co-worker explain why the tables were "designed" this way?

  • (cs)
    Alex Papadimoulis:
    Instead of using "CustomerId" columns on several of the tables, the application uses ... tables ... with the CustomerId appended to the end of the table. I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.

    One long database query maybe, but many calls to the directory functions to get the table filenames.  Since the latter call do not get considered, the design is considered efficient.  Is that about it?

    Sincerely,

    Gene Wirchenko


  • (cs) in reply to frosty

    Oh dear God!  I thought relational databases were day one of all CS degrees!

  • (cs)

    Stuff like this seems to happen alot. It also seems to get posted on TheDailyWTF alot as well. Gotta say that I'm not surprised after seeing it a billion times.

    The real WTF: The fact I can't think of anything witty for the real wtf!

  • (cs)
    Alex Papadimoulis:

    "Yeah, that application -- it's a little funny," his coworker replied...



    I get the feeling Michael failed to share his coworker's sense of mirth.
  • (cs)
    Alex Papadimoulis:

    "Yeah, that application -- it's a little funny," his coworker replied,

    <FONT face=Georgia>To quote the young boy in "Slingblade":</FONT>

    <FONT face=Georgia>"Not funny as in ha-ha, but funny as in queer."</FONT>

    <FONT face=Georgia>>BiggBru</FONT>

  • (cs) in reply to firewireguy
    firewireguy:
    Oh dear God!  I thought relational databases were day one of all CS degrees!


    You have never been in one of those degrees things, have you?

    </sarcasm>
  • Your Name is missing (unregistered) in reply to Gene Wirchenko
    Gene Wirchenko:
    Alex Papadimoulis:
    Instead of using "CustomerId" columns on several of the tables, the application uses ... tables ... with the CustomerId appended to the end of the table. I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.

    One long database query maybe, but many calls to the directory functions to get the table filenames.  Since the latter call do not get considered, the design is considered efficient.  Is that about it?

    Sincerely,

    Gene Wirchenko

    Uhhhhhh.... no, duh! You have an xml file on the server that has a list of all the tables.

  • APAQ11 (unregistered)

    I think he needs a table in that database dedicated to keeping track of all the other tables.

    I can't even imagine what he was possibly doing that made him get 3200+ tables. Probably where most people would just insert a row into a table he was just inserting a whole table or something like that.

  • Unklegwar (unregistered) in reply to firewireguy
    firewireguy:
    Oh dear God!  I thought relational databases were day one of all CS degrees!


    I dunno about now, but not what I got mine. Relational Databases was an elective. Which I didn't take (instead I snored thru Networks).

    Still, despite a lack of formal training, it is possible to turn out good databases just by using a mere 13 brain cells....

    I cry for Michael.
  • naterkane (unregistered)

    same thing at the company that i just left today!
    they have 305+ domains, and at one point had 305+ databases... all for the same basic site.
    it took them 2-3 years to bring it all together, which left the now "current" main DB with a few tabes for each site, and tons of relational tables that are only used to coalesce a #siteID#-#uniqueID# for each account.
    when i got there the 6 years of legacy code and almost zero documentation made me want to kill myself.

  • (cs)

    To the credit of the original designer, SQL Enterprise Manager loads slowly for me even when the database has 20 tables in it.

    But I digress. When I started working in ASP years ago, I was doing SELECT and INSERT statements for a long time before I got comfortable enough to move on to CREATE TABLE and stuff like that. This guy had to do extra research, and it never occurred to him to just have this number in a separate column. Now that's Javascript-enterprisey-brillant-FILENOTFOUND-ish.

  • Mark H (unregistered) in reply to APAQ11

    When I see WTFs like this, the only conclusion I can come to is the "application designer" has never seen an RDBMS with a good data model...maybe he is self-taught and just hacked together this solution as it was the best thing he could think of.

  • Anonymous Coward (unregistered)

    This seems to be very common - even Wordpress.com (http://photomatt.net/2006/03/01/wordpress-and-lyceum/) does this. WTF aside, is there any real reason why this is a good idea?

  • Boris Zakharin (unregistered) in reply to firewireguy

    Third year course at University of Pennsylvania. Not a required one at that.

  • (cs)

    [sarcasm] That way is better because he could track any changes to the record in the same table as the record! [/sarcasm]

    id 1 is original record.  id 2 is first update ;-)

    Maybe he hadn't learned the WHERE clause yet so he was stuck with:

    EXEC ('SELECT address1 FROM infoset' + @intCustomerID)

  • (cs) in reply to Boris Zakharin
    Anonymous:
    Third year course at University of Pennsylvania. Not a required one at that.


    At Northwestern University (the real one in Evanston, IL), there's only one DB class, it's optional (as are the compiler, OS, and networking classes; you pick 15 from a pool of classes), and it's taught by whoever gets stuck with it (atleast, 2 years ago this was the case).
  • (cs) in reply to Boris Zakharin

    When I got my degree, RDBMS was an elective, but I digress...

    I wonder if this was designed for a customer base on the order of 10, and then the business took off? No excuse for it, of course, but I've seen systems designed for one audience, and then have them pushed waaaaaaaay beyond the designers' original expectations (just a couple days ago, wasn't there a post about 500 sql cnx from an excel spreadsheet?)

    Imagine the nightmare of adding a column to the table-design - 34xx times? (smirks)

  • LizardFoot (unregistered)

    Sr. Dev #1:  Hey man, check this out.  I gave that new intern Matthew a task to do some work on the "funny" application.
    Sr. Dev #2:  Michael, I think the new intern's name is Michael. 
    Sr. Dev #1:  Whatever.  You should see him now.
    Sr. Dev #2:  Why?  What's he doing?
    Sr. Dev #1:  He's just walking the halls muttering "three thousand four hundred twenty seven tables" over and over.
    Sr. Dev #2:  Heh... man, you're pure evil.
    Sr. Dev #1:  Yeah, I know.  Wanna go get a latte?
    Sr. Dev #2:  Sure, you buying?


  • (cs) in reply to DiamondDave
    DiamondDave:
    So, did Michael's co-worker explain why the tables were "designed" this way?


    I daresay it's because his "coworker" was an "English Major."

    No alternative options hold up.
  • Dale Williams (unregistered)

    The Horror.... The Horror.....

  • Russell (unregistered) in reply to Boris Zakharin

    DBMS is a required course at Rutgers (300 level) but it is also one that most students do incredibly poorly in, to the point that the grading curve/scale is insane.

    Example:  On the midterm, I got a 47 out of 80.  I was hoping that this would be a C (I didn't prepare enough for the exam).  Turns out that this was an A.  Anything 20 or higher was passing.  So even though the class is required, learning is optional.

  • Dave (unregistered) in reply to frosty
    Anonymous:
    Didn't we have one similar to this a ways back (as in, within the last year or so)?

    It was where they created a table for everything that should be a "row" so that they wouldn't be constrained by a change in the DB schema.

    Either way, I'm not complaining.  You could have one of these twice a year and I'd be happy because it'd be keeping this design off the streets... maybe.


    It could be worse.....they could've set their tables up like Remedy....
  • Russell (unregistered) in reply to LizardFoot

    Anonymous:
    Sr. Dev #1:  Hey man, check this out.  I gave that new intern Matthew a task to do some work on the "funny" application.
    Sr. Dev #2:  Michael, I think the new intern's name is Michael. 
    Sr. Dev #1:  Whatever.  You should see him now.
    Sr. Dev #2:  Why?  What's he doing?
    Sr. Dev #1:  He's just walking the halls muttering "three thousand four hundred twenty seven tables" over and over.
    Sr. Dev #2:  Heh... man, you're pure evil.
    Sr. Dev #1:  Yeah, I know.  Wanna go get a latte?
    Sr. Dev #2:  Sure, you buying?

     

    There's something very 'BOFH' about this reply.   

  • YourName (unregistered)

    C'mon, this is brilliant.  Looking up an infoset by customer id is an O(1) operation, as opposed to the O(log n) search if the id were a column.   It's a performance optimization.  Just not a very good one.

  • diaphanein (unregistered) in reply to firewireguy

    firewireguy:
    Oh dear God!  I thought relational databases were day one of all CS degrees!

    Not hardly.  Granted I was computer engineering, not computer science, so I basically only took the core programming classes, everything else was hardware.  Still, I interned for 3 years as the personal code monkey the database administration group at a large US corporation.  I learned good database design through those years of being mentored by a dozen DBAs who collectively had a couple centuries of experience with all the major players (MS, Sybase, Oracle, IBM and others).

    The only way I would say that partitioning the dataset like this would make sense is that if each customer had a hideous amount of data in each table (I'm talking tens of millions of rows or more).  Then, and only then, could this possibly be justified.  Otherwise, I'd say this is another case of premature "optimization".

  • (cs) in reply to Russell
    Anonymous:

    DBMS is a required course at Rutgers (300 level) but it is also one that most students do incredibly poorly in, to the point that the grading curve/scale is insane.

    Example:  On the midterm, I got a 47 out of 80.  I was hoping that this would be a C (I didn't prepare enough for the exam).  Turns out that this was an A.  Anything 20 or higher was passing.  So even though the class is required, learning is optional.

    Really? It wasn't that bad for us. I had it spring 2004 w/ Borgida. By this point, CS enrollment was down enough that he could grade "normally" instead of doing it by percentages.

    I mean, I didn't do that well in it, but still. That sounds awful funny.

    I might add that I work with databases every day with my current job. It certainly prepared me, at least to some degree.

  • Unkown Coder (unregistered)

    So, there are numerous reasons why a system could look like that,

    the most obvious is, Are the customer's able to dictate their own data format?

    Lets say they have 700 customers, and the customers have their own data format, they don't want to glue all the customers to one huge table with the fields [customer id, fieldname, value], so they create tables to map to the customer's data requirments. This allows the customer to use sql to scan their own tables, to have indexes for their data and all other nice little features. Over time, this causes there to be many, many tables in the system, all with different schemas.

    I do think there probably (hopefuly) is a table that holds [tablename, customer_id] and perhaps another with [tablename, field_id, field_type]. If you go so far to add to the first table with [tablename, customer_id, database, login], you can even seperate customers to different database machines/clusters you could limit each customer to a tablespace and limit their visibility to each other's data.

    But then perhaps, the ASP model is lost on many people here.

  • (cs) in reply to pinguis
    pinguis:
    firewireguy:
    Oh dear God!  I thought relational databases were day one of all CS degrees!


    You have never been in one of those degrees things, have you?

    </sarcasm>


    Yeah, in my experience the average CS department wouldn't deign to teach anything as "vocational" as how to use a relational database.  I had a fun time at my first real job.
  • (cs) in reply to codeman
    codeman:

    I wonder if this was designed for a customer base on the order of 10, and then the business took off?

    Well, yeah. We started with 4 customers to be exact, but back then each customer had its own Excel spreadsheet. When we got to 18 customers, an outside consultant suggested we move to a fancy enterprisey database. So we did.

    So what's the WTF? Aren't we supposed to use a database?

    BTW, here's a helpful hint for others migrating from Excel to a database: You can save a lot of time just copying each entire spreadsheet contents into one big field in each table. No tedious copying and pasting of each cell value!

    --Rank

  • (cs)

    Maybe the programmer wanted better performance when processing a customer and didn't realise about that rarely used feature known as indices.

    I'm not going think about that.

  • mr foo (unregistered) in reply to Bus Raker

    EXEC ('SELECT address1 FROM infoset' + @intCustomerID)

    Yeah, that's a great idea! I never liked cached execution plans anyways!

  • An apprentice (unregistered)
    Alex Papadimoulis:

    I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.

    Why, it's simple. They just used a bunch of shell scripts to loop over all possible indices and to aggregate and postprocess the data. They're Enterprise after all ;-)

    I'm wondering if it's possible to query this monstrosity any "normal" way???

  • Harry (unregistered)

    <shudder>I have seen this done for large tables as a performance optimization when the company is too cheap to buy the Oracle partitioning package. Of course they never consider what the development costs  implied by this are.</shudder>


  • Harry (unregistered) in reply to An apprentice
    Anonymous:
    Alex Papadimoulis:

    I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.

    Why, it's simple. They just used a bunch of shell scripts to loop over all possible indices and to aggregate and postprocess the data. They're Enterprise after all ;-)

    I'm wondering if it's possible to query this monstrosity any "normal" way???



    The table schema aren't shown, but if you put the ID in the table as well as the table name and then use UNION ALL to string the tables together you are good to go for a normal query.

    Yes, I work for an enterprise company. <The Shame>


  • (cs)

    I wonder what such people would do with Oracle's partioned tables feature...

  • Vicki (unregistered)
    Alex Papadimoulis:

    Michael Atlas was pretty excited to land and an internship at a local software company, especially one as prestigious as to have "Enterprise" in their name. ...

    "Yeah, that application -- it's a little funny," his coworker replied, "but it actually works pretty well."



    Serendipitoous timing. We just got a copy of "Enterprise Integration with Ruby", by Maik Schmidt (publ. The Pragmatic Bookshelf).

    From the Introduction:
    "Have you ever  worked for a big enterprise? Do you remember your expectations as you walked into work on that first day? Whistling as the sun shone brightly, you might have been thinking, 'It will be great to workk for <company name here>. They will have a professional environment where coffee is free and where every system has been specified accurately, implemented carefully, and tested thoroughly. ..."

    "After your fifth cup of free coffee (around 9:07) you came to realize that the real world looks completely different from your expectations."

  • (cs)

    What I want to know is what >Bad< ideas the guy threw out as not serviceable...
    On second thought, maybe I don't want to know.

    In regards to  the discussion about DB design being mandatory, at the college I attended (Worcester State College) it was a requirement for graduation.

  • (cs)
    Alex Papadimoulis:

     I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.




    select Jobs from Monster.com where EmployeeSatisfaction > 1
  • (cs) in reply to Harry

    Anonymous:
    The table schema aren't shown, but if you put the ID in the table as well as the table name and then use UNION ALL to string the tables together you are good to go for a normal query.

    Yes, I work for an enterprise company. <The Shame>

    Um, UNION ALL for hundreds or thousands of tables? <shudder/>

  • BrokenArrow (unregistered) in reply to Russell
    Anonymous:

    DBMS is a required course at Rutgers (300 level) but it is also one that most students do incredibly poorly in, to the point that the grading curve/scale is insane.

    Example:  On the midterm, I got a 47 out of 80.  I was hoping that this would be a C (I didn't prepare enough for the exam).  Turns out that this was an A.  Anything 20 or higher was passing.  So even though the class is required, learning is optional.



    Curve grading it nice until you get people outside the nice curve. I was taking my drafting class and out of 35 students we had 3 professional draftsmen. Amazingly enough they scored 135% of the possible points, while the rest of us were running around the low 80 mark.
  • (cs)
    Alex Papadimoulis:

    I'll leave it as an exercise for the reader to envision the query needed to aggregate all "infoset" tables.



    In a stored procedure , query INFORMATION_SCHEMA for all table names that match 'infoset%'.  Aggregate those together in a dynamic SQL statement like 'select * from infoset1 union select * from infoset2 union...', then execute the dynamic SQL and return.  Piece o'cake, and I'm sure it will run in less than 10 minutes on a reasonably fast machine.
  • ChiBikeAndy (unregistered) in reply to YourName

    Huh...  I don't know how those "enterprise" dbms solutions handle primary keys, but Postgresql makes an implicit unique index for PKs.  I would imagine SQL server 2000 would handle that in a similar fashion.

    This arrangement might speed up operations when certain sets of data are locked for editing, because, you know, nobody in their right mind would want to use row locking! 

  • (cs) in reply to firewireguy
    firewireguy:
    Oh dear God!  I thought relational databases were day one of all CS degrees!

    Erm, what?

    Software engineering degrees or similar, maybe.

  • (cs)

    Everyone here should join together and create a consulting firm called the GarbageMen.  And have ads like this:

    "Does your newly hired DBA, Sysadmin, or Web Developer emit a blood curtling scream when looking at your system for the first time?  If the answer is yes, the GarbageMen will clean up your system better than new, or India."

  • (cs) in reply to BrokenArrow
    Anonymous:
    Anonymous:

    DBMS is a required course at Rutgers (300 level) but it is also one that most students do incredibly poorly in, to the point that the grading curve/scale is insane.

    Example:  On the midterm, I got a 47 out of 80.  I was hoping that this would be a C (I didn't prepare enough for the exam).  Turns out that this was an A.  Anything 20 or higher was passing.  So even though the class is required, learning is optional.



    Curve grading it nice until you get people outside the nice curve. I was taking my drafting class and out of 35 students we had 3 professional draftsmen. Amazingly enough they scored 135% of the possible points, while the rest of us were running around the low 80 mark.


    And here I am thinking that non-sensical clichés like "we gotta give 110% effort" were just figures of speech.
  • (cs) in reply to pjabbott

    In a stored procedure , query INFORMATION_SCHEMA for all table names that match 'infoset%'.  Aggregate those together in a dynamic SQL statement like 'select * from infoset1 union select * from infoset2 union...', then execute the dynamic SQL and return.  Piece o'cake, and I'm sure it will run in less than 10 minutes on a reasonably fast machine.


    No, no, no.  That's way too much work.  Just use a cursor on the Information schema tables.  And if the infoset tables don't all have exactly the same columns, well, you can do a cursor in each infoset table, and store the results in a master temp table that you can (wait for it) run a cursor over to deliver the results to the client.  Bonus points if you can nest them all in a mass cpu-eating orgy of wanton resource waste.


    select Jobs from Monster.com where EmployeeSatisfaction > 1

    Jobs
    ----------------

    (0 row(s) affected)

  • (cs) in reply to Manni
    Manni:

    To the credit of the original designer, SQL Enterprise Manager loads slowly for me even when the database has 20 tables in it.

    But I digress. When I started working in ASP years ago, I was doing SELECT and INSERT statements for a long time before I got comfortable enough to move on to CREATE TABLE and stuff like that. This guy had to do extra research, and it never occurred to him to just have this number in a separate column. Now that's Javascript-enterprisey-brillant-FILENOTFOUND-ish.




    <FONT face=Tahoma>i agree, i think SELECT statements were the first lessons, followed by INSERT, UPDATE, DELETE, then those DDL statements. That way you would probably learn to filter than create a separate table for each record or wreck havoc on production tables.

    but like what some said, i think it's some sort of optimization (if no infoset aggregation is required) or customization (if each infoset has its own implementation for each client)

    whatever the *real* reason behind this design, i'll probably stick with xml tables :P

    </FONT>

Leave a comment on “It's a Little Funny”

Log In or post as a guest

Replying to comment #:

« Return to Article