CodeSOD: Foreign to Administration

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!