Don't Be Evil

« Return to Article
  • ubersoldat 2013-08-14 06:35
    The evil's wishes are hard to come by.
  • PK 2013-08-14 06:36
    name varchar(20) primary key

    That was enough for me..
  • coward 2013-08-14 06:40
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.
  • JimmyCrackedCorn 2013-08-14 06:41
    My WTF List:
    - developers that don't understand normalization
    - DBAs that participate, or worse don't understand normalization.
    - No mention of the use of views (perhaps the database didn't support optimized views?)

    wftery abounds.
  • Mike 2013-08-14 06:49
    The manager that hires a clueless tosser as a DBA
  • FragFrog 2013-08-14 06:54
    PK:
    name varchar(20) primary key

    That was enough for me..

    Yeah, I thought that was bad too, then I read the next bit:

    admin_user1 varchar(20) not null,

    Clearly, nobody there including the DBA's have the slightest clue about database normalization. Which begs the question: what exactly is it that the DBA's there do?
  • JC 2013-08-14 06:55
    JimmyCrackedCorn:
    My WTF List:
    - developers that don't understand normalization
    - DBAs that participate, or worse don't understand normalization.
    - No mention of the use of views (perhaps the database didn't support optimized views?)

    wftery abounds.


    Don't forget the Not Null constraints on all the admin columns on the application_admins table.
    I'm sure if an application has fewer than 5 admins they populate the rest with spaces or something equally horrendous.
  • Hannes 2013-08-14 07:11
    So, what do they do if there are more than 5 admins for a given application? Create a new table with 10 possible admin names?
  • Paddles 2013-08-14 07:22
    I know why you're leaving. Your ClueBat has been used so much it's now a ClueToothpick.
  • Matteo 2013-08-14 07:28
    FragFrog:
    PK:
    name varchar(20) primary key

    That was enough for me..

    Yeah, I thought that was bad too, then I read the next bit:

    admin_user1 varchar(20) not null,

    Clearly, nobody there including the DBA's have the slightest clue about database normalization. Which begs the question: what exactly is it that the DBA's there do?


    ...and not content with that:

    listen_port integer not null unique,

    I am not saying it's a good idea (you should use a separate primary key, what if a service changes port?), but at least this one is an integer, unique and not null... so if you wanted to be sloppy, at least use this as the primary key.

    And for heaven's sake, if you are so crazy to use a varchar as a pk, at least put some foreign key constraints around. It's not a solution to anything, but at least it will stop you from shooting your foot.

    Ugh.
  • ubersoldat 2013-08-14 07:46
    PK:
    name varchar(20) primary key

    That was enough for me..


    Ha! And what happens when the same user has admin rights to different applications? PKs HAVE TO BE UNIQUE!!!
  • Dahaka 2013-08-14 07:49
    ubersoldat:
    PK:
    name varchar(20) primary key

    That was enough for me..


    Ha! And what happens when the same user has admin rights to different applications? PKs HAVE TO BE UNIQUE!!!


    Isn't that the name of the application?
  • Ronald 2013-08-14 07:52
    coward:
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.


    It is pretty obvious that the name column in that table would be the application name, not the user. So using it as a primary key is a minor WTF (the name could change or there could be multiple versions) but TRWTF is people laughing at a simple data model that they don't understand.
  • Perkele 2013-08-14 07:59
    Beautiful example.
    I worked for a company which developed software for medical health care specialized on InVitroFertilisation.
    The had a table like this:
    create table Patient (
    PatientId INTEGER NOT NULL,
    PatientFirstname VARCHAR(20),
    PatientLastname VARCHAR(20),
    ...
    BabyName VARCHAR(20),
    BabyDateOfBirth DATE,
    BabySex INT, --- (SIC!!!)
    Baby2Name VARCHAR(20),
    BabyDateOfBirth2 DATE,
    Baby2Sex INT
    );

    Some day we had a patient with 3 children. Our chief developer decided to change the architecture as follows:

    create table Patient (
    PatientId INTEGER NOT NULL,
    PatientFirstname VARCHAR(20),
    PatientLastname VARCHAR(20),
    ...
    BabyName VARCHAR(20),
    ...
    Baby2Name VARCHAR(20),
    ...
    Baby3Name VARCHAR(20)
    );

    A few months later we had a patient with 4 children. Therefore the chief developer decided to change the architecture to something more scalable.

    create table Patient (
    PatientId INTEGER NOT NULL,
    OtherPatientID INTEGER NOT NULL,
    PatientFirstname VARCHAR(20),
    PatientLastname VARCHAR(20),
    ...
    BabyName VARCHAR(20),
    ...
    Baby2Name VARCHAR(20),
    ...
    Baby3Name VARCHAR(20)
    );

    OtherPatientID referenced to a PatientId. So whenever a patient had more than 3 children we had to create a new dataset and link it to the original patient.
    This was genius!
    The chief developer had been fired. But not because of his genius architectures.
    So, whenever you plan to get a 4th child by in vitro fertilization you should ask for the hospital's database design :)
  • radarbob 2013-08-14 08:01
    I was there when it first rolled out! I had to legally change my name seven times.
  • QJo 2013-08-14 08:05
    Cluebat. What a lovely work I've learned.

    "Many things in life can be joined. You can join two pieces of wood to construct something useful. Like a double-jointed super-leverage extra-power cluebat."
  • Finiderire 2013-08-14 08:05
    Hi,

    I am still wondering how they arrived to this :
    left outer join (select name, MIN(admin1) as admin1
    from application_admins
    group by name) as AD1 using name
    left outer join (select name, MAX(admin2) as admin2
    from application_admins
    group by name) as AD2 using name
    left outer join (select name, MAX(admin3) as admin3
    from application_admins
    group by name) as AD3 using name
    left outer join (select name, MAX(admin4) as admin4
    from application_admins
    group by name) as AD4 using name
    left outer join (select name, MAX(admin5) as admin5
    from application_admins
    group by name) as AD5 using name
    My best answer is that they brutally copy/paste the second part of the previous query.

    http://img.tapatalk.com/3281f1ff-4eef-8823.jpg

    a+, =)
    -=Finiderire=-
  • Steve The Cynic 2013-08-14 08:05
    Mike:
    The manager that hires a clueless tosser as a DBA

    Sounds to me like most of the devs (except perhaps for Doug K himself) also fall into the category of "clueless tosser".

    And perhaps Doug himself deserves a couple of FAIL points, for creating the particular query. Really, dude, it isn't that hard to explain to people how to properly query a database, even if you've worn the ClueBat down beyond ClueToothpick to ClueSplinter.
  • QJo 2013-08-14 08:05
    QJo:
    Cluebat. What a lovely work I've learned.

    "Many things in life can be joined. You can join two pieces of wood to construct something useful. Like a double-jointed super-leverage extra-power cluebat."


    I mean, what a lovely word I've learned.
  • Meep 2013-08-14 08:09
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)

    JimmyCrackedCorn:
    My WTF List:
    - No mention of the use of views (perhaps the database didn't support optimized views?)


    Any view is dropped into the join plan, so it's optimized with the rest of the query. Unless by "optimized" you mean "materialized."
  • DaveK 2013-08-14 08:16
    Hannes:
    So, what do they do if there are more than 5 admins for a given application? Create a new table with 10 possible admin names?
    They probably select the last admin in alphabetical order five more times on top of the four times they already did...

  • Ollie 2013-08-14 08:24
    There are a finite number of Normal Forms for database design. That's boring, but help is on the way! There are an infinite, and maybe even uncountable number of Abnormal Forms.
  • AnotherMike 2013-08-14 08:26
    Maybe Manager == useless tosser == DBA?
  • huppenzuppen 2013-08-14 08:30
    http://en.wikipedia.org/wiki/Beg_the_question
  • ANON 2013-08-14 08:32
    But to be fair even the select Doug gave to them is ugly:

    select applications.name, attribute, listen_port, AD1.admin1, AD2.admin2
    from applications
    left outer join (select name, MIN(admin) as admin1
    from application_admins
    group by name) as AD1 using name
    left outer join (select name, MAX(admin) as admin2
    from application_admins
    group by name) as AD2 using name
    };

    This would do the same:

    select applications.name, attribute, listen_port, AD.admin1, AD.admin2
    from applications
    left outer join (select name, MIN(admin) as admin1, MAX(admin) as admin2
    from application_admins
    group by name) as AD using name

    };
  • DaveAronson 2013-08-14 08:41
    A DBA goes into a bar, walks up to two tables, and says, "May I join you?"
  • EvilSnack 2013-08-14 08:49
    I know almost nothing about databases, and I can see some (but not all) of the problems here.

    CAPTCHA distineo: From the chorus when a boy band sings about destiny.
  • Oslo 2013-08-14 08:49
    admin_users varchar(210)

    Insert comma-separated list. Done.
  • Captain Oblivious 2013-08-14 08:49
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Worse than failure.

    Instead of having a "variable number of blahs" in a single record, you can have a table of blahs which points to the original, blah-less record. You can then "join" the table of blahs to the record.

    That is called "normalization", since it puts the relevant tables into a "normal form".
  • JayGee 2013-08-14 08:50
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Did you hear that sound? It's the sound of this whole entire WTF going over your head.
  • Sam 2013-08-14 09:00
    coward:
    PK:
    name varchar(20) primary key

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    Why are you renaming an application? Isn't that what release numbers are for?

    TRWTF is naming an application "Dave from accounts".
  • cyborg 2013-08-14 09:03
    DaveAronson:
    A DBA goes into a bar, walks up to two tables, and says, "May I join you?"


    A DBA goes into a bar, walks up to two tables, and walks out again utterly confused by the situtation.
  • Argh Argh I'm Dying You Idiot 2013-08-14 09:04
    DaveAronson:
    A DBA goes into a bar, walks up to two tables, and says, "May I join you?"
    Ow! Ow! No puns this early the morning!

    Captcha: tego - when you break out the puns, I got tego.
  • luptatum 2013-08-14 09:15
    Right or wrong, this is simply how table joins have always been done.
  • JimmyCrackedCorn 2013-08-14 09:23
    JayGee:
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Did you hear that sound? It's the sound of this whole entire WTF going over your head.


    As a public service. There are minor variations that can be used, but a quick mysql definition where one record in the applications table has an applicationID=1 and name='MyApplication':


    /* mysql implementation of two tables related one to many. */

    CREATE TABLE IF NOT EXISTS `applications` (
    `applicationID` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `attribute` varchar(20) DEFAULT NULL,
    `listen_port` int(11) DEFAULT NULL,
    PRIMARY KEY (`applicationID`),
    UNIQUE KEY `listen_port` (`listen_port`)
    );


    CREATE TABLE IF NOT EXISTS `users` (
    `userID` int(11) NOT NULL,
    `applicationID` int(11) NOT NULL,
    `username` varchar(20) NOT NULL,
    `isadmin` int(1) NOT NULL,
    PRIMARY KEY (`userID`),
    KEY `applicationID` (`applicationID`)
    );

    /* two queries that would get all the admin users from an application */

    select a.username from users a, applications b where
    a.applicationID=b.applicationID and
    b.name='MyApplication' and
    a.isadmin=1

    select username from users where applicationID=1 and isadmin=1;




  • Krunt 2013-08-14 09:26
    Captain Oblivious:
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Worse than failure.

    Instead of having a "variable number of blahs" in a single record, you can have a table of blahs which points to the original, blah-less record. You can then "join" the table of blahs to the record.

    That is called "normalization", since it puts the relevant tables into a "normal form".


    +1.

    Massive lulz at Meep's post also. Unless it was meant seriously, in which case I feel really sorry for him.
  • Craig 2013-08-14 09:29
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for." Well, when you hire junior people this is the kind of software you get because they don't understand the fundamentals. They may be able to crank our PHP, C#, Java, or whatever the language du jour is, but ask them about OO principles or database normalization and they look at you like you're speaking gibberish. I weep for the future of our profession.
  • Craig 2013-08-14 09:32
    Meep:
    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Please, please tell me you're joking. Alternatively, please send me your real name so if we ever get your resume I know not to even waste time reviewing it. Thank you.
  • Krunt 2013-08-14 09:42
    Meep:

    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.


    Lol, yeah that's why I like to make all my columns XML based so I can fit whatever sequences of data I want into them.

    You joker, you ;-)
  • Anom nom nom 2013-08-14 09:50
    listen_port integer     not     null unique,


    And so it was that two different web applications would never listen on port 80, and port numbers greater than 80 grew up and procreated.
  • ¯\(°_o)/¯ I DUNNO LOL 2013-08-14 10:07
    coward:
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.
    TRWTF is having more than one application named "Dave".
  • Scourge of Programmers! 2013-08-14 10:20
    Craig:
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for." Well, when you hire junior people this is the kind of software you get because they don't understand the fundamentals. They may be able to crank our PHP, C#, Java, or whatever the language du jour is, but ask them about OO principles or database normalization and they look at you like you're speaking gibberish. I weep for the future of our profession.


    Getting job done at the right price is more important!
  • Scourge of Programmers! 2013-08-14 10:21
    Anom nom nom:
    listen_port integer     not     null unique,


    And so it was that two different web applications would never listen on port 80, and port numbers greater than 80 grew up and procreated.


    Are you having some kind of sixth sense? For all you know, they stored IIS as the application running on port 80.

    Don't be an idiot in the vast sea of idiots.
  • Hannes 2013-08-14 10:22
    ¯\(°_o)/¯ I DUNNO LOL:
    coward:
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.
    TRWTF is having more than one application named "Dave".


    All my applications are named HAL plus a consecutive number. Right now, I am at HAL 8500.
  • Jeremy 2013-08-14 10:59
    JC:
    JimmyCrackedCorn:
    My WTF List:
    - developers that don't understand normalization
    - DBAs that participate, or worse don't understand normalization.
    - No mention of the use of views (perhaps the database didn't support optimized views?)

    wftery abounds.


    Don't forget the Not Null constraints on all the admin columns on the application_admins table.
    I'm sure if an application has fewer than 5 admins they populate the rest with spaces or something equally horrendous.


    They can be blank/empty, but not NULL. We generally do that to avoid any wrinkles of having to do "blank or null" checks.
  • TheSoftwareDev 2013-08-14 11:00
    Mike:
    The manager that hires a clueless tosser as a DBA


    I read this as "clueless toaster".
  • ¯\(°_o)/¯ I DUNNO LOL 2013-08-14 11:00
    Craig:
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for."
    Ah yes, the thinly-veiled age discrimination. Translation: "We don't want you because you're not young enough to be fooled into working 80-hour weeks."
  • baguazhang 2013-08-14 11:03
    There are probably several toasters which could design a better table structure than the one in this article!!!
  • Vincent Jansen 2013-08-14 11:15
    He forgot to prefix the columns, now you can only have 10 admin's max, he should have used admin001, be be safe.
  • J. Strange 2013-08-14 11:19
    And the database engine, ever the responsible designated driver, says, "Not so fast--let me get your keys first."
  • Steve 2013-08-14 11:21
    At least they broke it out into a separate table. :-)

    Last company I was at... We had a table which contained 4 columns which were used as accumulators. There was another table which had a list of what would be summed up for each of these 4 columns.

    Accumulator1
    Accumulator2
    Accumulator3
    Accumulator4

    Someone decided that the users may not want to be limited by 4 columns. They may want more. They may want 36 options!

    So the developer added additional columns to the table.

    Accumulator1
    Accumulator2
    Accumulator3
    Accumulator4
    ...
    Accumulator0
    AccumulatorA
    AccumulatorB
    ...
    AccumulatorZ

    When I saw this, I just about flipped out. It was another year and a half before I finally quit. I kept telling myself I could improve things. :-)
  • DCRoss 2013-08-14 11:27
    ...they got a DBA to help...

    Wait a minute. Are we sure this is a Snoofle story? Because I have never seen him use those two words together.
  • Ritsaert 2013-08-14 12:00
    So the first admin is optional and admin_user2 is mandatory? How logical.
  • cellocgw 2013-08-14 12:05
    coward:
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.


    Dave's not here, man!

    TRWTF is you need to get "Bob from AccountTemps, Knurlman."
  • clively 2013-08-14 12:09
    For the love of god stop letting developers *design* anything. They can't. We know this. Sure they can type in stuff called "code" which usually does a half ass job of making peoples lives better and a full ass job of making them want to rip the developer to pieces.

    Development managers are even worse. They will put together an "architecture" that has more to do with the tools they have a religious experience with than with anything resembling actual business requirements.

    I've seen too many applications put together by developers with little to no oversight. Usually a business requirement will come up, that was completely foreseeable but apparently wasn't something the dev even considered: like the example in the story. Then, rather than "fix" the problem the right way, they spend hours arguing how it is too hard to change or not "feasable" or what have you then proceed to make things worse.
  • Mandy Ttory 2013-08-14 12:14
    Still using SQL and relational databases? Haven't people learned anything?
  • Dan 2013-08-14 12:37
    ¯\(°_o)/¯ I DUNNO LOL:
    Craig:
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for."
    Ah yes, the thinly-veiled age discrimination. Translation: "We don't want you because you're not young enough to be fooled into working 80-hour weeks."


    Or just "we don't want to pay you what you're worth"
  • Popeye 2013-08-14 12:57
    Definition of a DBA:
    A failed developer who still thinks he/she can write code.
  • Jay 2013-08-14 12:59
    The rules of normalization say that if you have a repeating group, you should break this out to a separate table.

    So that's what they did. The repeating group is now in another table.
  • Jay 2013-08-14 13:01
    Dan:
    ¯\(°_o)/¯ I DUNNO LOL:
    Craig:
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for."
    Ah yes, the thinly-veiled age discrimination. Translation: "We don't want you because you're not young enough to be fooled into working 80-hour weeks."


    Or just "we don't want to pay you what you're worth"


    Because if you have a choice between, say, buying a car that runs for $15,000 or a car that doesn't run for $10,000, obviously the second choice is the more efficient and economical one. Any discussion of how much it will cost to get it running, or if that is even possible, is irrelevant. It is a car, it is cheaper, therefore it is the better choice.
  • Jay 2013-08-14 13:02
    Mandy Ttory:
    Still using SQL and relational databases? Haven't people learned anything?


    Yes, I've learned that:

    (a) SQL sucks

    (b) Everything I've seen that's an alternative to SQL sucks bigger
  • JayGee 2013-08-14 13:10
    clively:
    For the love of god stop letting developers *design* anything. They can't. We know this. Sure they can type in stuff called "code" which usually does a half ass job of making peoples lives better and a full ass job of making them want to rip the developer to pieces.

    Development managers are even worse. They will put together an "architecture" that has more to do with the tools they have a religious experience with than with anything resembling actual business requirements.

    I've seen too many applications put together by developers with little to no oversight. Usually a business requirement will come up, that was completely foreseeable but apparently wasn't something the dev even considered: like the example in the story. Then, rather than "fix" the problem the right way, they spend hours arguing how it is too hard to change or not "feasable" or what have you then proceed to make things worse.


    So then who is going to design? You say developers can't. You say management can't. If you actually read the article, you know that DBA can't since they made it worse.

    All this story needed was someone with knowledge or experience to review the proposed database design at the beginning and say, this is a many to many relationship, it should be a cross reference. No more story.
  • tweek 2013-08-14 13:16
    Oslo:
    admin_users varchar(210)

    Insert comma-separated list. Done.


    That's how my ticketing system does it for a couple fields. Well, it's even better than that -- it's a semi-colon separated list. Reporting on this single large, very wide table is awesome.
  • chubertdev 2013-08-14 13:18
    Oslo:
    admin_users varchar(210)

    Insert comma-separated list. Done.


    Version 2:


    ALTER TABLE application_admins
    ALTER COLUMN admin_users VARCHAR(420) NOT NULL
  • ArrivingRaptor 2013-08-14 13:22
    Oslo:
    admin_users varchar(210)

    Insert comma-separated list. Done.

    I hate you.





    As well as whoever at my last project did this years ago to our report generation table (a table with over 1 bn rows, and the only reliable way to join the table was to regex the column for the other table's PK).
  • n+1 2013-08-14 13:33
    The max(admi1) is explained by the join using a group-by; so a function is required.

    But I can't explain the use of a group-by. Well actually I can in that they don't know what they are doing.

    I like this book: The Art of SQL, http://shop.oreilly.com/product/9780596008949.do
  • chubertdev 2013-08-14 13:41
    clively:
    For the love of god stop letting developers *design* anything. They can't. We know this. Sure they can type in stuff called "code" which usually does a half ass job of making peoples lives better and a full ass job of making them want to rip the developer to pieces.

    Development managers are even worse. They will put together an "architecture" that has more to do with the tools they have a religious experience with than with anything resembling actual business requirements.

    I've seen too many applications put together by developers with little to no oversight. Usually a business requirement will come up, that was completely foreseeable but apparently wasn't something the dev even considered: like the example in the story. Then, rather than "fix" the problem the right way, they spend hours arguing how it is too hard to change or not "feasable" or what have you then proceed to make things worse.


    what the hell are you talking about? you think that no developers know anything about databases just because of the clueless SOB in the story?
  • Dani 2013-08-14 13:41
    Sure, Oslo deserves a place in hell.

    I see this technique is quite common: Using varchar to store all kinds of beasts. They even name it "serialized data". Here's the contents of one of my rows (we use CLOBS directly).

    e[=]1[|]v[=]tx4[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]4[|]v[=]tx4[|]a[=]9[|]c[=]0[|]p[=]10[||]e[=]5[|]v[=]tx4[|]a[=]2[|]c[=]0[|]p[=]6[||]e[=]6[|]v[=]tx4[|]a[=]2[|]c[=]0[|]p[=]6[||]e[=]7[|]v[=]tx4[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]1[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]4[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]10[||]e[=]5[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]6[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]7[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]8[|]v[=]mch16[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]1[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]4[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]10[||]e[=]5[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]6[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]7[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]8[|]v[=]mch26[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]1[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]4[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]10[||]e[=]5[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]6[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]7[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]8[|]v[=]ch48[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]1[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]4[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]10[||]e[=]5[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]6[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]6[||]e[=]7[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]0[||]e[=]8[|]v[=]mv7[|]a[=]9[|]c[=]0[|]p[=]0
  • The_Assimilator 2013-08-14 13:43
    BREAKING NEWS: the majority of DBAs are clueless fucks who couldn't join their hands to their arses in the dark.

    In other news: water is wet, space is cold, and people are stupid.
  • chubertdev 2013-08-14 14:06
    Popeye:
    Definition of a DBA:
    A failed developer who still thinks he/she can write code.


    this is going to end well
  • ¯\(°_o)/¯ I DUNNO LOL 2013-08-14 14:55
    cellocgw:
    Dave's not here, man!

    TRWTF is you need to get "Bob from AccountTemps, Knurlman."
    Now all you have to is fit that into a VARCHAR(20).
  • Jake From Statefarm 2013-08-14 15:03
    coward:
    PK:
    name varchar(20) primary key

    That was enough for me..

    First there was "Dave".
    Then there was "Dave from accounts".
    Who quickly became "ManagerDave".
    Leaving "Dave from accounts" free for the next Dave from accounts.

    See... it works.


    Then there's "Jake".
    Then there's "Jake from StateFarm".
  • John Doe 2013-08-14 15:06
    Jay:
    Mandy Ttory:
    Still using SQL and relational databases? Haven't people learned anything?


    Yes, I've learned that:

    (a) SQL sucks

    (b) Everything I've seen that's an alternative to SQL sucks bigger


    What about serialized classes saved as files?
  • John Doe 2013-08-14 15:08
    chubertdev:
    clively:
    For the love of god stop letting developers *design* anything. They can't. We know this. Sure they can type in stuff called "code" which usually does a half ass job of making peoples lives better and a full ass job of making them want to rip the developer to pieces.

    Development managers are even worse. They will put together an "architecture" that has more to do with the tools they have a religious experience with than with anything resembling actual business requirements.

    I've seen too many applications put together by developers with little to no oversight. Usually a business requirement will come up, that was completely foreseeable but apparently wasn't something the dev even considered: like the example in the story. Then, rather than "fix" the problem the right way, they spend hours arguing how it is too hard to change or not "feasable" or what have you then proceed to make things worse.


    what the hell are you talking about? you think that no developers know anything about databases just because of the clueless SOB in the story?


    Look at StackOverflow. The majority of "n00bs" asking about SQL write horrible SQL
  • John Doe 2013-08-14 15:08
    chubertdev:
    Popeye:
    Definition of a DBA:
    A failed developer who still thinks he/she can write code.


    this is going to end well


    Well.
  • chubertdev 2013-08-14 15:16
    John Doe:
    Look at StackOverflow. The majority of "n00bs" asking about SQL write horrible SQL


    I think that if you wrote your response in complete sentences, it would justify your point better.

    It reads like this:

    John Doe:
    ERRMERRGERRDD the n00bz at StackOverflow write horrible SQL!!1!


    This post does nothing to differentiate developers and DBAs.
  • Anonypony 2013-08-14 15:26
    PK:
    name varchar(20) primary key

    That was enough for me..


    Me too. Sadly I get to work on a production database that has this in almost every table:

    FooID varchar(50) NOT NULL PRIMARY KEY


    The original "developers" and "DBA" wanted to use these cool new GUID things they heard about as the primary key for EVERY table. Unfortunately they had not also heard about UNIQUEIDENTIFIER and so obviously varchar is the next sensible choice.

    And of course these keys are all in a CLUSTERED INDEX.

    Sigh.

    Captcha: incassum -- Incassum da firum, breakum da glassum
  • da Doctah 2013-08-14 15:38
    Jay:
    Because if you have a choice between, say, buying a car that runs for $15,000 or a car that doesn't run for $10,000, obviously the second choice is the more efficient and economical one. Any discussion of how much it will cost to get it running, or if that is even possible, is irrelevant. It is a car, it is cheaper, therefore it is the better choice.


    This. I don't know how many times I've had some form of the following conversation:

    Boss: "Application XYZ doesn't work. How much will it cost to develop a replacement."

    Me: "Fifteen thousand."

    Boss: "But Application XYZ only cost twelve thousand! Can't you come down a little?"

    Me: "I thought you said Application XYZ doesn't work."

    Boss: "That's right."

    Me: "Oh, I misunderstood the requirements. I can develop something that doesn't work for ten thousand."
  • chubertdev 2013-08-14 15:44
    Anonypony:
    PK:
    name varchar(20) primary key

    That was enough for me..


    Me too. Sadly I get to work on a production database that has this in almost every table:

    FooID varchar(50) NOT NULL PRIMARY KEY


    The original "developers" and "DBA" wanted to use these cool new GUID things they heard about as the primary key for EVERY table. Unfortunately they had not also heard about UNIQUEIDENTIFIER and so obviously varchar is the next sensible choice.

    And of course these keys are all in a CLUSTERED INDEX.

    Sigh.

    Captcha: incassum -- Incassum da firum, breakum da glassum


    Why wouldn't you have a clusterf*ck in a clustered index?
  • anonymous 2013-08-14 15:55
    Reminds me of the time I had to do the reverse: the table had a whole bunch of numbered columns, and only a few of them were usually used. The resulting report only printed 2 records per page because it was printing all of those empty fields.

    After (very briefly) considering re-engineering the database to put each row in a separate table and link the tables with a key (it would have required completely redesigning the data entry form, which had lots of text boxes), I rolled up my sleeves and typed this instead:

    SELECT tbl.id, tbl.Date, 1 AS row,
    
    tbl.costcode1 as costcode, tbl.faccode1 as faccode, tbl.code1 as code, tbl.hrs1 as hrs, tbl.min1 as min,
    tbl.wonum1 as wonum, tbl.comments1 as comments, tbl.shift1 as shift, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode1 > "" OR comments1 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 2 AS row,
    tbl.costcode2, tbl.faccode2, tbl.code2, tbl.hrs2, tbl.min2,
    tbl.wonum2, tbl.comments2, tbl.shift2, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode2 > "" OR comments2 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 3 AS row,
    tbl.costcode3, tbl.faccode3, tbl.code3, tbl.hrs3, tbl.min3,
    tbl.wonum3, tbl.comments3, tbl.shift3, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode3 > "" OR comments3 > "")
    ...
    UNION ALL
    SELECT tbl.id, tbl.Date, 12 AS row,
    tbl.costcode12, tbl.faccode12, tbl.code12, tbl.hrs12, tbl.min12,
    tbl.wonum12, tbl.comments12, tbl.shift12, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode12 > "" OR comments12 > "")
    ORDER BY fname, lname, row;

    It looks ugly and takes longer to run (12 times longer, to be exact), but the resulting report uses a lot less paper.
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Holy hell 2013-08-14 16:06
    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?
  • Anon 2013-08-14 16:12
    JimmyCrackedCorn:
    JayGee:
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Did you hear that sound? It's the sound of this whole entire WTF going over your head.


    As a public service. There are minor variations that can be used, but a quick mysql definition where one record in the applications table has an applicationID=1 and name='MyApplication':


    /* mysql implementation of two tables related one to many. */

    CREATE TABLE IF NOT EXISTS `applications` (
    `applicationID` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `attribute` varchar(20) DEFAULT NULL,
    `listen_port` int(11) DEFAULT NULL,
    PRIMARY KEY (`applicationID`),
    UNIQUE KEY `listen_port` (`listen_port`)
    );


    CREATE TABLE IF NOT EXISTS `users` (
    `userID` int(11) NOT NULL,
    `applicationID` int(11) NOT NULL,
    `username` varchar(20) NOT NULL,
    `isadmin` int(1) NOT NULL,
    PRIMARY KEY (`userID`),
    KEY `applicationID` (`applicationID`)
    );

    /* two queries that would get all the admin users from an application */

    select a.username from users a, applications b where
    a.applicationID=b.applicationID and
    b.name='MyApplication' and
    a.isadmin=1

    select username from users where applicationID=1 and isadmin=1;






    Is this a joke I didn't get or is it meant seriously?
  • real-modo 2013-08-14 16:31
    Oslo:
    admin_users varchar(210)

    Insert comma-separated list. Done.


    If you're going to quote "SQL Antipatterns", you should really cite it. (That's antipattern #1, for readers.)

    TRWTF, for those devs, is that the "applications" table doesn't store the application admin password, unencrypted of course. (Antipattern #20).
    create table applications (
    
    ...
    admin_password varchar(8) default("admin"),
    ...

    ought to do it.
  • Tasty 2013-08-14 17:05
    ANON:
    But to be fair even the select Doug gave to them is ugly:

    select applications.name, attribute, listen_port, AD1.admin1, AD2.admin2
    from applications
    left outer join (select name, MIN(admin) as admin1
    from application_admins
    group by name) as AD1 using name
    left outer join (select name, MAX(admin) as admin2
    from application_admins
    group by name) as AD2 using name
    };

    This would do the same:

    select applications.name, attribute, listen_port, AD.admin1, AD.admin2
    from applications
    left outer join (select name, MIN(admin) as admin1, MAX(admin) as admin2
    from application_admins
    group by name) as AD using name

    };


    Why is everyone accepting the left outer join or any outer join? Every column in application_admins is not null.

    This can be done with a simple (inner) join:
    (Sure, you get only one admin per line, but it handles N-admins!)

    select applications.name, attribute, listen_port, AD.admin
    from applications join application_admins AD;
  • Tasty 2013-08-14 17:09
    anonymous:
    Reminds me of the time I had to do the reverse: the table had a whole bunch of numbered columns, and only a few of them were usually used. The resulting report only printed 2 records per page because it was printing all of those empty fields.

    After (very briefly) considering re-engineering the database to put each row in a separate table and link the tables with a key (it would have required completely redesigning the data entry form, which had lots of text boxes), I rolled up my sleeves and typed this instead:

    SELECT tbl.id, tbl.Date, 1 AS row,
    
    tbl.costcode1 as costcode, tbl.faccode1 as faccode, tbl.code1 as code, tbl.hrs1 as hrs, tbl.min1 as min,
    tbl.wonum1 as wonum, tbl.comments1 as comments, tbl.shift1 as shift, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode1 > "" OR comments1 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 2 AS row,
    tbl.costcode2, tbl.faccode2, tbl.code2, tbl.hrs2, tbl.min2,
    tbl.wonum2, tbl.comments2, tbl.shift2, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode2 > "" OR comments2 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 3 AS row,
    tbl.costcode3, tbl.faccode3, tbl.code3, tbl.hrs3, tbl.min3,
    tbl.wonum3, tbl.comments3, tbl.shift3, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode3 > "" OR comments3 > "")
    ...
    UNION ALL
    SELECT tbl.id, tbl.Date, 12 AS row,
    tbl.costcode12, tbl.faccode12, tbl.code12, tbl.hrs12, tbl.min12,
    tbl.wonum12, tbl.comments12, tbl.shift12, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode12 > "" OR comments12 > "")
    ORDER BY fname, lname, row;

    It looks ugly and takes longer to run (12 times longer, to be exact), but the resulting report uses a lot less paper.


    You're probably eating memory or disk swapping! Drop the ORDER BY and I'll bet it runs much faster.
  • Kuba 2013-08-14 17:32
    Craig:
    And when I go interview for jobs I'm told, "You're probably more senior than we're looking for." Well, when you hire junior people this is the kind of software you get because they don't understand the fundamentals. They may be able to crank our PHP, C#, Java, or whatever the language du jour is, but ask them about OO principles or database normalization and they look at you like you're speaking gibberish. I weep for the future of our profession.
    I would have hoped that the junior people still haven't forgotten that stuff from college, and haven't gotten so bogged down by family life as not to give a fuck anymore about "boring old school material who needs it anyway we're teh code jockeyz".

    If you need senior people just to know the basics that anyone fresh out of school should know, I start to weep a bit. Just a bit.
  • Raugturi 2013-08-14 17:37
    One can never tell. On the off chance that someone's reading this for a solution the best solution is 4 tables:

    applications:
    Columns: id, name, port (all not null)
    Primary Key: id
    Constraints: name and port are unique (or probably should be anyway)

    users:
    Columns: id, username, password (all not null)
    Primary Key: id
    Constraints: username is unique

    application_roles:
    Columns: id, application_id, rolename (all not null)
    Primary Key: id
    Constraints: combination of application_id and rolename are unique

    application_user_roles:
    Columns: id, application_id, user_id, role_id (all not null)
    Primary Key: id
    Constraints: combination of application_id and user_id is unique

    Now you can assign a user to multiple applications, assign multiple users of any roles you want to a single application, and implement more roles than just "admin" and "user" without breaking any other applications.

    Although if you're certain you'll always only have users and admins you could get away with 3 by ditching the roles table and replacing application_user_roles with this:

    application_users:
    Columns: id, application_id, user_id, is_admin (all not null)
    Primary Key: id
    Constraints: combination of application_id and user_id is unique

    But I can almost guarantee as soon as you do it someone is going to ask for some level of access between user and admin.
  • Raugturi 2013-08-14 17:38
    Anon:
    JimmyCrackedCorn:
    JayGee:
    Meep:
    JimmyCrackedCorn:
    developers that don't understand normalization


    What has normalization got to do with anything? They wanted to fit a variable number of admins in a field that only held one admin. That is a problem with the database interface, it has nothing to do with the table structure.

    (For instance, in Postgres, I'll never set up a table with an array column, but I'll often write a subquery that *returns* an array. That is completely kosher.)


    Did you hear that sound? It's the sound of this whole entire WTF going over your head.


    As a public service. There are minor variations that can be used, but a quick mysql definition where one record in the applications table has an applicationID=1 and name='MyApplication':


    /* mysql implementation of two tables related one to many. */

    CREATE TABLE IF NOT EXISTS `applications` (
    `applicationID` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `attribute` varchar(20) DEFAULT NULL,
    `listen_port` int(11) DEFAULT NULL,
    PRIMARY KEY (`applicationID`),
    UNIQUE KEY `listen_port` (`listen_port`)
    );


    CREATE TABLE IF NOT EXISTS `users` (
    `userID` int(11) NOT NULL,
    `applicationID` int(11) NOT NULL,
    `username` varchar(20) NOT NULL,
    `isadmin` int(1) NOT NULL,
    PRIMARY KEY (`userID`),
    KEY `applicationID` (`applicationID`)
    );

    /* two queries that would get all the admin users from an application */

    select a.username from users a, applications b where
    a.applicationID=b.applicationID and
    b.name='MyApplication' and
    a.isadmin=1

    select username from users where applicationID=1 and isadmin=1;






    Is this a joke I didn't get or is it meant seriously?


    One can never tell. On the off chance that someone's reading this for a solution the best solution is 4 tables:


    applications:
    Columns: id, name, port (all not null)
    Primary Key: id
    Constraints: name and port are unique (or probably should be anyway)

    users:
    Columns: id, username, password (all not null)
    Primary Key: id
    Constraints: username is unique

    application_roles:
    Columns: id, application_id, rolename (all not null)
    Primary Key: id
    Constraints: combination of application_id and rolename are unique

    application_user_roles:
    Columns: id, application_id, user_id, role_id (all not null)
    Primary Key: id
    Constraints: combination of application_id and user_id is unique


    Now you can assign a user to multiple applications, assign multiple users of any roles you want to a single application, and implement more roles than just "admin" and "user" without breaking any other applications.

    Although if you're certain you'll always only have users and admins you could get away with 3 by ditching the roles table and replacing application_user_roles with this:


    application_users:
    Columns: id, application_id, user_id, is_admin (all not null)
    Primary Key: id
    Constraints: combination of application_id and user_id is unique


    But I can almost guarantee as soon as you do it someone is going to ask for some level of access between user and admin.
  • Coyne 2013-08-14 17:45
    JimmyCrackedCorn:
    My WTF List:
    - developers that don't understand normalization
    - DBAs that participate, and, worse, don't understand normalization.
    - No mention of the use of views (perhaps the DBA didn't didn't know about optimized views?)

    wftery abounds.


    FTFY
  • Kuba 2013-08-14 17:45
    John Doe:
    chubertdev:
    what the hell are you talking about? you think that no developers know anything about databases just because of the clueless SOB in the story?
    Look at StackOverflow. The majority of "n00bs" asking about SQL write horrible SQL
    Sorry to rain on your parade, Sherlock, but if they knew SQL well, they wouldn't ask, right?
  • Friedrice The Great 2013-08-14 17:57
    John Doe:
    chubertdev:
    Popeye:
    Definition of a DBA:
    A failed developer who still thinks he/she can write code.


    this is going to end well


    Well.

    "All's well that ends in a well," the well-digger said.
  • Barf4Eva 2013-08-14 18:01
    select AllWork, NoPlay, Johnny, DullBoy, J1.johnny1, J2.johnny2, J3.johnny3, J4.johnny4, J5.johnny5, J6.Johnny6, J7.Johnny7, J8.Johnny8, J9.Johnny9
    from JohnnyDullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny1) as johnny1
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J1 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny2) as johnny2
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J2 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny3) as johnny3
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J3 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny4) as johnny4
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J4 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny5) as johnny5
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J5 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny6) as johnny6
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J6 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny7) as johnny7
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J7 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny8) as johnny8
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J8 using DullBoy
    left outer join (select DullBoy, MIN(Here_Is_Johnny9) as johnny9
    from Johnny
    where Work = 1 AND Play = 0
    group by DullBoy) as J9 using DullBoy
  • pjt56 2013-08-14 18:22
    No it doesn't: http://en.wikipedia.org/wiki/Begging_the_question
  • chubertdev 2013-08-14 18:48
    Kuba:
    John Doe:
    chubertdev:
    what the hell are you talking about? you think that no developers know anything about databases just because of the clueless SOB in the story?
    Look at StackOverflow. The majority of "n00bs" asking about SQL write horrible SQL
    Sorry to rain on your parade, Sherlock, but if they knew SQL well, they wouldn't ask, right?


    80/20 rule. 80% of developers can't program, 80% of DBAs aren't normal.

    see what I did there?
  • Norman Diamond 2013-08-14 22:13
    snoofle:
    Since their time was short and the developers were leary of modifying the code that referenced this table, Doug gave them the following query (with comments):
    Did I read this right? The developers were leary but the DBA was the one on acid?
  • Bill C. 2013-08-14 22:17
    snoofle:
    You can join with a person of your choosing. Joins can be good things. Sometimes...
    Right or wrong, that's how we did it back in the days when we did the choosing and the intern's opinion didn't matter.
  • Matt 2013-08-15 05:28
    [quote user="FragFrog"][quote user="PK"]
    Clearly, nobody there including the DBA's have the slightest clue about database normalization. Which begs the question: what exactly is it that the DBA's there do?[/quote]

    They take the specs from the customer, and they bring them to the engineers.....
  • anonymous 2013-08-15 13:08
    Tasty:
    anonymous:
    Reminds me of the time I had to do the reverse: the table had a whole bunch of numbered columns, and only a few of them were usually used. The resulting report only printed 2 records per page because it was printing all of those empty fields.

    After (very briefly) considering re-engineering the database to put each row in a separate table and link the tables with a key (it would have required completely redesigning the data entry form, which had lots of text boxes), I rolled up my sleeves and typed this instead:

    SELECT tbl.id, tbl.Date, 1 AS row,
    
    tbl.costcode1 as costcode, tbl.faccode1 as faccode, tbl.code1 as code, tbl.hrs1 as hrs, tbl.min1 as min,
    tbl.wonum1 as wonum, tbl.comments1 as comments, tbl.shift1 as shift, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode1 > "" OR comments1 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 2 AS row,
    tbl.costcode2, tbl.faccode2, tbl.code2, tbl.hrs2, tbl.min2,
    tbl.wonum2, tbl.comments2, tbl.shift2, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode2 > "" OR comments2 > "")
    UNION ALL
    SELECT tbl.id, tbl.Date, 3 AS row,
    tbl.costcode3, tbl.faccode3, tbl.code3, tbl.hrs3, tbl.min3,
    tbl.wonum3, tbl.comments3, tbl.shift3, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode3 > "" OR comments3 > "")
    ...
    UNION ALL
    SELECT tbl.id, tbl.Date, 12 AS row,
    tbl.costcode12, tbl.faccode12, tbl.code12, tbl.hrs12, tbl.min12,
    tbl.wonum12, tbl.comments12, tbl.shift12, emp.lname, emp.minit, emp.fname
    FROM emp INNER JOIN [tbl] ON emp.id = tbl.id
    WHERE tbl.Date = [Forms]![Main]![dtm]
    AND (costcode12 > "" OR comments12 > "")
    ORDER BY fname, lname, row;

    It looks ugly and takes longer to run (12 times longer, to be exact), but the resulting report uses a lot less paper.


    You're probably eating memory or disk swapping! Drop the ORDER BY and I'll bet it runs much faster.
    I can't. The names need to be in alphabetical order, and the line items have to be in the same order as they were entered originally (which is the reason behind 1 AS row, 2 AS row, etc. and then ORDER BY ... row).

    It's not really that much data, anyway; it's mainly just slow because, well, Access.
  • Harrow 2013-08-15 21:58
    I wish you would show a little more sensitivity. I have a son who is a DBA, and let me tell you it is no laughing matter.
  • I kill them 2013-08-16 06:38
    Harrow:
    I wish you would show a little more sensitivity. I have a son who is a DBA, and let me tell you it is no laughing matter.


    I am sorry to learn about your retarded son...
  • Jay 2013-08-16 13:29
    da Doctah:
    Jay:
    Because if you have a choice between, say, buying a car that runs for $15,000 or a car that doesn't run for $10,000, obviously the second choice is the more efficient and economical one. Any discussion of how much it will cost to get it running, or if that is even possible, is irrelevant. It is a car, it is cheaper, therefore it is the better choice.


    This. I don't know how many times I've had some form of the following conversation:

    Boss: "Application XYZ doesn't work. How much will it cost to develop a replacement."

    Me: "Fifteen thousand."

    Boss: "But Application XYZ only cost twelve thousand! Can't you come down a little?"

    Me: "I thought you said Application XYZ doesn't work."

    Boss: "That's right."

    Me: "Oh, I misunderstood the requirements. I can develop something that doesn't work for ten thousand."


    +1 I'll be plagiarizing this in a future staff meeting.
  • Seahen 2013-08-19 10:38
    TRWTF is using this title for an article that isn't about Google.
  • Neil 2013-08-20 09:30
    Tasty:
    anonymous:
    It looks ugly and takes longer to run (12 times longer, to be exact), but the resulting report uses a lot less paper.
    You're probably eating memory or disk swapping! Drop the ORDER BY and I'll bet it runs much faster.
    I'll bet it takes you more than 12 times longer to cut up the pieces of paper and reassemble them in order.
  • Neil 2013-08-20 09:37
    Tasty:
    ANON:
    But to be fair even the select Doug gave to them is ugly:
    select applications.name, attribute, listen_port, AD1.admin1, AD2.admin2 
    
    from applications
    left outer join (select name, MIN(admin) as admin1
    from application_admins
    group by name) as AD1 using name
    left outer join (select name, MAX(admin) as admin2
    from application_admins
    group by name) as AD2 using name
    This would do the same:
    select applications.name, attribute, listen_port, AD.admin1, AD.admin2 
    
    from applications
    left outer join (select name, MIN(admin) as admin1, MAX(admin) as admin2
    from application_admins
    group by name) as AD using name


    Why is everyone accepting the left outer join or any outer join? Every column in application_admins is not null.
    That would be true if you were interested in the admins and happened to want to know the attribute and port of the application, but not if you were interested in the application and any admins it may or may not have. Anyway, here's another alternative, but you should of course profile it to find out whether it outperforms the previous suggestions:
    select applications.name, attribute, listen_port, MIN(admin) AS admin1, MAX(admin) AS admin2 
    
    from applications
    left outer join application_admins using name
    group by applications.name, attribute, listen_port
  • justme 2013-09-26 16:05
    clively:
    For the love of god stop letting developers *design* anything. They can't. We know this. Sure they can type in stuff called "code" which usually does a half ass job of making peoples lives better and a full ass job of making them want to rip the developer to pieces.

    Development managers are even worse. They will put together an "architecture" that has more to do with the tools they have a religious experience with than with anything resembling actual business requirements.

    I've seen too many applications put together by developers with little to no oversight. Usually a business requirement will come up, that was completely foreseeable but apparently wasn't something the dev even considered: like the example in the story. Then, rather than "fix" the problem the right way, they spend hours arguing how it is too hard to change or not "feasable" or what have you then proceed to make things worse.


    Are you sure we didn't just work on a project together ? I have a "rock-star" programmer who thinks he can architecture.