has_* for booleans, and
*_at for timestamps. Beyond that we are pretty inconsistent, mostly because my thoughts keep evolving. At the moment I’m inclined to agree with
<entity>_id though we have an awful lot of primary keys just named
id and our analysts are used to that. What I don’t like about it is that I’ve had several cases of junior analysts just joining all tables by id instead of understanding the foreign keys, but that is mostly a matter of training and unfamiliarity with sql. Still, explicit is better than implicit.
For counts I like
*_count, feels more English language intuitive to me. For money I use an explicit
currency_code column if one exists, and suffix with
*_usd if it doesn’t.
When there is a compound key I create a surrogate by concatenation and then almost always just name it
row_key to indicate it shouldn’t join to anything.
My overriding concerns are understandability of the data model, and analyst ergonomics, so I shy away from making final tables feel “programmer-y” with prefix and suffix notation like in your example.