Learning is not a spectator sport

Yeah, it was a hideous bug-ridden piece of junk, and best thing was … it didn’t really matter.

First let me explain what led to my terrible app. Good mate Dom Giles posted this message on our internal Slack channel the other day.

“I need to give myself a goal, and I noticed that Cancer Research UK is doing the 100 press-ups a day challenge for April. So doing this will benefit others as I fail hopelessly at it. But I’d also like to drag as many people into this wretched cause in the hope they will succeed when I don’t. I’m looking for other volunteers to commit to the challenge. So who’s up for it? 100 press-ups a day in April!!!
https://www.cancerresearchuk.org/get-involved/find-an-event/100-push-ups-a-day-challenge
”

There was plenty of banter back and forth on the thread, as we all poked fun at each other and our supposed “enthusiasm” for such a challenge . But some momentum was built and soon enough there were a number of intrepid souls ready to take it on. At which point Dom posted:

and I thought to myself: “I reckon I can have a crack at that”.

Initially I thought – I probably just need a single table. Each time someone wants to log some pushups they’ve done, I just need their name, the date/time, and the amount of pushups they did. Which yielded this:

create table pushups ( 
  email            varchar2(255) not null,
  logged_datetime  date          not null,
  amount           number(3)     not null
);

Then I thought a bit more about it. We have people from all over the world doing this challenge, and they each are in different time zones. It’s a bit rich to ask people to log data in UTC, so I wanted to let people nominate what time zone they were in. So that gave rise to a PEOPLE table to sit above the PUSHUPS table. Here inside Oracle, your email is also your single sign-on username, so that would be an easy thing to grab when people authenticate to my app.

create table people (
  email      varchar2(255) not null,
  tzname     varchar2(64),
);  

Now I needed a list of valid time zones, so I simply grabbed that from our v$timezone_names view. Since I now had a dedicated people table, I may as well let them add their names as well to make any future display of the data more friendly.

create table tzones ( tzname varchar2(64));
insert into tzones 
select distinct tzname from v$timezone_names;

create table people (
  email      varchar2(255) not null,
  first_name varchar2(255),
  last_name  varchar2(255),
  tzname     varchar2(64),
);  

And of course the moment I did this, it dawned on me that even though people love contributing to a team goal, they still may feel a little hesitant about sharing their personal results with the rest of the team, so they should be able to log their data, contribute to the team total, but not have their results displayed.

create table people (
  email      varchar2(255) not null,
  first_name varchar2(255),
  last_name  varchar2(255),
  tzname     varchar2(64),
  private    varchar2(1) default 'N' 
);  

A little tweaking here and there and I ended up with the following data model.

At this point, I had three things very clear in my mind

1. I’m going to whip up an APEX app to let people add data,
2. My first cut at this will probably have bugs,
3. I’m a data guy, I like my data to be clean and correct.

Even though my “customers” for this app are all work colleagues, it is one thing for your app to be a little rough around the edges, but it is whole different ball game if the data being collected is garbage. These are people’s pushups we’re talking about here!

Thus I added the following constraints to the model

On the PEOPLE table

• Email is a primary key.
• The email must like ‘%@oracle.com’. If it’s not, it likely means that the app has passed over incorrect information from single sign-on.
• Whilst first name / last name are optional, I enforce that the upper case concatenation of the two must be unique. Once again, if my app sends through the same person twice with a garbled email, I’m hoping to catch that.
• The time zone nominated by someone is a foreign key back to the TZNAMES table.
• The PRIVATE flag can only be “Y” or “N”.

On the PUSHUPS table

• EMAIL/LOGGED forms a primary key for the table to avoid any duplicates slipping through.
• EMAIL is foreign key back to the PEOPLE table so we can never get orphans.
• The LOGGED_DATE must be between March 30th and May 1st in UTC because the challenge runs through April, and I want to guard against my app sending incorrect date to the database.
• The AMOUNT must be between 0 and 500. I don’t really expect someone to be able to do 500 pushups in a single attempt, but perhaps someone might sum up their attempts for a day and log it as one entry. The important thing is that if my app is buggy, no-one can log millions of pushups, or negative pushups.

Then I jumped into APEX and started building my app and here is the crucial thing. As I was writing my app, even though APEX did much of the heavy lifting for me, there were still plenty of places where I made silly little errors. A typo here and there, and I’m incorrectly trying to jam a NULL into the AMOUNT, or incorrect format masks meant I was trying to put incorrect date information into the LOGGED_DATE.

But…it…did…not…matter.

Because every time I did that, the rules I had placed in the database saved me from myself. It immediately told me about the bugs I had in my application.

All of the rules you put in your data model – the data types, their sizes and precision, the constraints … do you know what these are?

These are unit tests for your data and ultimately your app.

With a few iterations, my app is now working as it should be. Because I’m not UI expert, it is definitely not thing a of beauty, but it is functionally correct, and more importantly, if there are places where it might still not be functionally correct, there is an excellent chance that the database will not let those bugs corrupt the most critical thing, namely, people’s data.

Dom was naturally thrilled with this development

So if you’re a developer that thinks it is important to have a rigorous unit testing framework for your application, but also thinks that data validity rules aren’t needed in the database (or are overkill), then respectfully I suggest there’s a level of hypocrisy in trying to have both of those views concurrently.

Post script: This post was motivated by a common chestnut that appeared on Linkedin last week