Blogmark

Use UUID Primary Keys

I don’t have strong opinions on this one. Most of the systems I work on would do just as well with bigints as UUIDs. Now that v7 UUIDs are generally available, there is an even stronger argument to go that direction since they are sortable.

Give everything createdat and updatedat

I absolutely agree with this. These columns should be used not as app logic (except sorting), but for auditing and investigation purposes. I’ve always needed these at one point or another to look into some production data issue or support request.

on update restrict on delete restrict

It’s good to have default practices for this kind of thing, but absolutes aren’t that useful. Sometimes you want a cascade and as long as you’ve thought through the details, that may be the right choice.

Use schemas

The default public schema is great. Using alternative schemas usually means having to fight conventions in web frameworks like Rails. It might be worth it, but know that you’ll generally be cutting against the grain if you go this direction.

Enum Tables

Enum tables are a great pattern, but I would caution against using a text value as the primary key. Use some size of bit value that won’t change even if the text value does. That way you don’t have to make a bunch of updates in referencing tables.

Name your tables singularly

I see no benefit to singular naming. I prefer plural naming because of my time using Rails which establishes that as a convention. Whichever direction you go, make sure to be consistent.

Mechanically name join tables

I think this is a good practice to follow. Migration tools like Rails’ ActiveRecord often do this in a deterministic way for you. Occasionally it is nice to have a perfectly named join table based on domain, but you inevitably falter when trying to later remember what tables it joins. It’s a small thing, but consistency generally wins.

Almost always soft delete

This used to be my stance for any core application tables. However since listening to the Soft Deletes episode of Postgres.fm, I’ve realized it’s way more nuanced than I thought and you often don’t want soft deletes. It’s worth a listen.

…