• Steve (unregistered)

    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. :-)

  • (cs)
    ...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 (unregistered)

    So the first admin is optional and admin_user2 is mandatory? How logical.

  • (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.

    Dave's not here, man!

    TRWTF is you need to get "Bob from AccountTemps, Knurlman."

  • (cs)

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

    Still using SQL and relational databases? Haven't people learned anything?

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

    Definition of a DBA: A failed developer who still thinks he/she can write code.

  • Jay (unregistered)

    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 (unregistered) in reply to Dan
    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 (unregistered) in reply to Mandy Ttory
    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 (unregistered) in reply to clively
    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.

  • (cs) in reply to Oslo
    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.

  • (cs) in reply to Oslo
    Oslo:
    admin_users varchar(210)

    Insert comma-separated list. Done.

    Version 2:

    ALTER TABLE application_admins
ALTER COLUMN admin_users VARCHAR(420) NOT NULL
  • (cs) in reply to Oslo
    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 (unregistered)

    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

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

    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

  • (cs)

    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.

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

    Then there's "Jake". Then there's "Jake from StateFarm".

  • John Doe (unregistered) in reply to Jay
    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 (unregistered) in reply to chubertdev
    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 (unregistered) in reply to chubertdev
    chubertdev:
    Popeye:
    Definition of a DBA: A failed developer who still thinks he/she can write code.

    this is going to end well

    Well.

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

  • (cs) in reply to Jay
    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."

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

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

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Holy hell (unregistered)

    Holy hell how many tdwtf programmers for a it take to fix the html encoding on the RSS feed?

  • Anon (unregistered) in reply to JimmyCrackedCorn
    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 (unregistered) in reply to Oslo
    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 (unregistered) in reply to ANON
    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 (unregistered) in reply to anonymous
    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.

  • (cs) 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.
    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.

  • (cs) in reply to Anon

    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.

  • (cs) in reply to Anon
    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.

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

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

    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

Leave a comment on “Don't Be Evil”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article