Doug's co-workers were creating a database to manage a set of custom application services and their associated metadata. So one of them threw together this table:

create table applications (
  name        varchar(20) primary key,
  attribute   varchar(20) not null,
  listen_port integer     not null unique,
  admin_user varchar(20) not null
)

This was fine, until one of the users decided they wanted a second admin for an application. So someone went ahead and updated the table:

create table applications (
  name        varchar(20) primary key,
  attribute   varchar(20) not null,
  listen_port integer     not null unique,
  admin1 varchar(20) not null,
  admin2 varchar(20)
)

Doug helpfully pointed out that they could normalize this data and store the list of admins as a table with foreign keys. Something like:

create create table application_admins (
  name        varchar(20) references applications,
  admin       varchar(20) not null
);

Now, they could just join the tables and have an arbitrary number of admins per application.

Everyone agreed this was probably the right way to do it, but all of the code which depended on the table needed the columns to be admin1 and admin2. Sure, that code could be updated, but what might break? It was too risky.

So Doug helpfully provided this query, comment included, to imitate the existing schema:

-- Um, this is evil.  Really, you should to do a separate query against the
-- admins table to get the list of admins for each list, instead of this
-- ugly ugly query.
  select admins.name, attribute, listen_port, admin1, admin2
  from admins
  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 AD1 using name

This was fine until someone realized that they needed more than two admins per application. So they helpfully updated the application_admins table.

create create table application_admins (
  name        varchar(20) references applications,
  admin1      varchar(20) not null,
  admin2      varchar(20) not null,
  admin3      varchar(20) not null,
  admin4      varchar(20) not null,
  admin5      varchar(20) not null
);

And then modified the query to match:

  select admins.name, attribute, listen_port,
         admin1, admin2, admin3, admin4, admin5
  from admins
  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

These modifications, of course, were unnecessary, especially the table change, which isn't just unnecessary, it's just wrong. The way relationships work in databases appears to be foreign to some people.

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!