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:

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.

Remy Porter

Source link

You May Also Like

Best of 2022: Special Validation

Ah, password validation, how can that possibly go wrong? Original —Remy Ah,…

Canada Has Been Ecologically Decimated to Provide The Military Bearskin Hats for the Queen’s Funeral

Wednesday, 14 September 2022 The Queen died in her 90’s. All of…

Unusually Numerous

We had an unusual number (not that unusual, it was an integer)…

ICYMI June 2022: Issue #20 – Farming, Fan Rooms, Free Meals, and More!

Welcome back for another edition of In Case You Missed It, our…