I went on a little day drinking session with some friends a few weekends ago. As the conversation shifted to football I found my mind wandering to other things. We'd had a conversation earlier in the day about how this was "going to be a long one" and we reckoned "we're going to knock a few back today". So I got to thinking, how many?

I'd like to preface this entire article by saying I don't recommend drinking excessively, you should always follow health guidelines for the country you live in. Don't be an idiot (like me!)

My initial method started as a spreadsheet, with names in one column and "drinks" in the other column. It would operate as a relatively arbitrary number, a pint would be one drink, a double with mixer two.

The issue with this, however, is that it doesn't accurately represent the alcohol content in each drink. Not even close. In the UK, a popular method of measuring alcohol content of drinks is in "units". It's a simple way of expressing how much pure alcohol is in a drink. One unit is 10ml or 8g of pure alcohol. About how much the average adult can process in an hour.

My original method was completely flawed, because I was under the misconception that a double would twice the alcohol content as a pint. It turns out, they're equal.

One train journey later, I had come up with a solution.

PocketBase is an open source backend built with Go. It's a wrapper around SQLite with authentication, file storage, realtime and a REST API built in. It's an incredibly cool project and it's also open source. The best feature, at least for me at the time, is the admin backend. You can administer the entire database from a really nice web UI.

What's more, you can spin up an instance of PocketBase really easily using PocketHost (not an ad). Even better, the first instance is free!

So, I fired up a PocketBase instance, got logged in and started working on my table.

Database Design

I decided to leverage relationships for the project. I'd have a table for drinks, table for people and a pivot table to link the two together. I could then use a View Collection in PocketBase to show a summary of the data.

First up, the people table. I only needed to add a single text field for the person's name.

Next, the drinks table. Drinks also have names. I also need to store the units for a drink as a number. I added a category field as well, because I thought it would be cool to see where the group's preferences were.

Finally, the pivot table. I'm following the Laravel naming convention here. Naming it after the two "models" I'm joining in alphabetical order, so it's called drink_person.

This has two relationship fields, person and drink, as well as an optional picture column. I never actually used this, but I thought that if I were to ever turn this into an app, it might be cool if users could upload a picture of each drink they've had so they have something to look back on while they're wallowing in hangover regret the next day.

Now, when we ordered a round, all I had to do was fill in the drinks table for any drink that wasn't already stored and assign it to a person. Up front, there's quite a bit of work involved, but that should taper off as the night goes on.

Summarizing Data

I was starting to get a little bit tipsy when I realised I hadn't come up with the summary table yet. View Collections in PocketBase are a nicely formatted result of an SQL query you can use to display information in different ways.

I needed to (quickly) come up with a query that would show me the total units consumed for each person. ChatGPT gave me a non-functional base query, which I corrected to give me this:

SELECT p.name AS person_name, SUM(dr.units) AS units_total, p.id AS id
FROM people p
INNER JOIN drink_person dp ON p.id = dp.person
INNER JOIN drinks dr ON dp.drink = dr.id
GROUP BY p.id;

To explain, we start at the people table. We join in the drink_person table, linking the people.id column to drink_person.person. Then we use another join to link drink_person.drink to the drink.id column. This means we can use all of the information in the drink column for each person.

We sum up the drink units, group by the person ID so there's only one result per person, job done.

The Wall of Shame

The final result looks something like this. I've redacted my friends' names for their sakes. Some of the numbers could be squiffy, I was doing a very quick Google search for each drink's unit contents.

The project was a success, in perhaps more ways than one. I looked up the NHS recommended intake for alcohol after this. They say no more than 14 units per week, spread across 3 days or more. We'd more than doubled, in some cases tripled that over the span of 16-18 hours.

I had considered making this into a little app, where you can get fun stats from your night out the day after. Like Spotify Wrapped but for your weekend. Not sure I want to bring ranked competitive drinking into the world though.

To be clear, none of us went into this going for a high score, and I certainly don't recommend you do that either. We did put a few away though.