Now vs. Then: How I would build it now vs. how I would have built it way back when

Incremental progress is hard to measure. In an effort to reflect on the knowledge i've gained in the last year, I compare a naive implementation of a simple app I might have come up with a year or two ago, with how I would build it today.

Problem:

Design an application to keep track of and report tips for a restaurants wait staff

What should the schema look like?

Version 1:

CREATE TABLE waiters (
    id int,
    name varchar,
    tip_total int
);

In this version of the application, when a tip is collected, it is entered into the system. The row for the corresponding wait staff to include an updated total.

This SQL might look something like:

  SELECT *  FROM waiters WHERE id=:id;

  # Application logic calculates new values 
  # Something like: new_amount = result[2] + amount

 UPDATE waiters SET tip_total=:new_amount WHERE id=:id;

Reporting tips at the end of the night is then as simple as:

SELECT name, tip_total FROM waiters;

Then after all the tips are split up, I can go ahead and zero out the count to be ready for the next day. Easy as pie.

UPDATE waiters SET tip_total = 0;

But wait, what about waiters splitting tips for a table?

How about just two sequential select / update statements where splitting the update amount is managed by the application logic.

Version 2

CREATE TABLE waiters (
    id int,
    name varchar
);

CREATE TABLE tips (
    id int,
    staff_id int,
    currency_code varchar,
    amount int,
    time datetime,
    FOREIGN KEY (staff_id) REFERENCES waiters(id)
);

In this version of the application, when a tip is added to the system we will insert a row into the Tips table. Something like:

 INSERT INTO tips VALUES (1, :table, :usd, :now)

Reporting now becomes slightly more complicated. In order to report tips for a single member of the wait staff I need to:

SELECT SUM(amount), currency_code, CONVERT( Date, time) as d GROUP BY currency_code where staff_id = :staff_id AND d = :today 

Which will give me a table of all currencies I accepted tips in, which I will then need to convert to USD in application logic.

What are the differences between these two approaches?

Certainly both versions are very far from being a real life production quality applications, but I'd like to dig into some of the reasons I believe version 2 is better.

Sins from most egregious to least:

1. By relying on deleting data to maintain an accurate count, we lose the ability to look back in time and do any kind of historical reporting.

2. By depending on getting the most up to date version of the tip total in order to calculate the new value, we introduce a nasty multiple writer race condition that could lose tips

3. We are trying to maintain an aggregate manually within the database. This limits our ability to do future reporting.

4. In the case of a split tip, version 1 doesn't use transactions. This means process death or an untimely exception could only partially apply a tip.

How version 2 implementation deals with these issues:

1. Some people probably think it seems goofy to even include this. I can guarantee that the version of me who had hardly touched a database would have thought it was fine, I mean hey, it fulfills the application requirements. The primary fault I see with this though is that even though it fulfills the requirements of the application right now, it is not very future proof. As soon as you ask the question, what is my average tips per night? You're kind of out of luck, the data is already gone. Another less substantiated down side is that deleting data is scary. If you get it wrong there's no getting it back, alternatively, if you push a version of your app with the wrong query, one patch version later you're back in business.

2. Arguably the most egregious sin introduced by maintaining this sum manually is the way we calculate it. We do a read, then use our application logic to update the column with a new number. What happens if two thread are concurrently trying to write?

Well.. if we're unlucky, something like this:

# Bob has $15 in tips
Thread 1: Reads current tip total of $15
Thread 2: Read current tip total of $15
Thread 1: Calculates new values should be $17, and updates the database
Thread 2:  Calculates new values should be $16, and updates the database

# Final value: Bob has $16 in tips

Uh oh... we just dropped the first $2 tip.

We could fix this by introducing some careful locking. Setting the transaction isolation level to REPEATABLE_READ and using a SELECT... FOR UPDATE would probably get us there. That is a lot of complexity that is easy to get wrong though. Let's just insert tips into their own table. Concurrent addition of rows to a table isn't going to cause us any grief. Databases can deal with that.

1. Databases are pretty good at generating aggregate statistics. Lets leave that to the database. We are maintaining our our SUM function, but what if we want the mean? Or the median? Let's keep the data in the database, and perform calculations based on the data.

2. My example didn't really highlight transactions too much, but I would say the biggest thing i've learned in the last year is that using transactions is important. In toy app land, it can be fairly easy to have avoid complicated queries and updates that whose consistency must be enforced. In real app land, queries tend to get complicated, and real users tend to have a way of finding those tricky edge cases. Transactions are a basic construct that lets you group interactions with the database. From what I sit currently, if you're doing a bunch of related things to your database, you probably ought to make sure they are grouped within the same transaction to ensure consistency of both reads and writes.

All in all, sometimes knowledge tends to sit around and accumulate without me really noticing. It is nice to sit back once in a while and reflect on some of that new stuff in your head.

Happy Coding