• (cs)

    The evil's wishes are hard to come by.

  • PK (unregistered)

    name varchar(20) primary key

    That was enough for me..

  • coward (unregistered) in reply to PK
    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 (unregistered)

    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 (unregistered)

    The manager that hires a clueless tosser as a DBA

  • (cs) in reply to PK
    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 (unregistered) in reply to JimmyCrackedCorn
    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 (unregistered)

    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?

  • (cs)

    I know why you're leaving. Your ClueBat has been used so much it's now a ClueToothpick.

  • Matteo (unregistered) in reply to FragFrog
    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.

  • (cs) in reply to PK
    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!!!

  • (cs) in reply to ubersoldat
    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?

  • (cs) in reply to coward
    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 (unregistered)

    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 (unregistered)

    I was there when it first rolled out! I had to legally change my name seven times.

  • QJo (unregistered)

    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 (unregistered)

    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=-

  • (cs) in reply to Mike
    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 (unregistered) in reply to QJo
    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 (unregistered) in reply to JimmyCrackedCorn
    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."

  • (cs) in reply to Hannes
    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 (unregistered)

    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 (unregistered) in reply to Mike

    Maybe Manager == useless tosser == DBA?

  • huppenzuppen (unregistered) in reply to FragFrog

    http://en.wikipedia.org/wiki/Beg_the_question

  • ANON (unregistered)

    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

    };

  • (cs)

    A DBA goes into a bar, walks up to two tables, and says, "May I join you?"

  • EvilSnack (unregistered)

    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 (unregistered)

    admin_users varchar(210)

    Insert comma-separated list. Done.

  • Captain Oblivious (unregistered) in reply to Meep
    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 (unregistered) in reply to Meep
    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 (unregistered) in reply to coward
    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 (unregistered) in reply to DaveAronson
    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 (unregistered) in reply to DaveAronson
    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 (unregistered)

    Right or wrong, this is simply how table joins have always been done.

  • JimmyCrackedCorn (unregistered) in reply to JayGee
    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 (unregistered) in reply to Captain Oblivious
    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 (unregistered)

    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 (unregistered) in reply to Meep
    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 (unregistered) in reply to Meep
    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 (unregistered) 
    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 (unregistered) in reply to coward
    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! (unregistered) in reply to Craig
    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! (unregistered) in reply to Anom nom nom
    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 (unregistered) in reply to ¯\(°_o)/¯ I DUNNO LOL
    ¯\(°_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 (unregistered) in reply to JC
    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.

  • (cs) in reply to Mike
    Mike:
    The manager that hires a clueless tosser as a DBA

    I read this as "clueless toaster".

  • ¯\(°_o)/¯ I DUNNO LOL (unregistered) in reply to Craig
    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 (unregistered) in reply to TheSoftwareDev

    There are probably several toasters which could design a better table structure than the one in this article!!!

  • Vincent Jansen (unregistered)

    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 (unregistered) in reply to DaveAronson

    And the database engine, ever the responsible designated driver, says, "Not so fast--let me get your keys first."

Leave a comment on “Don't Be Evil”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article