Hacker News Reader

Comment by out_of_protocol | Hacker News Reader

smallint looks like a good alternative, with dictionary in the app or separate table. So far i've only seen storing dictionary in app source code approach

View on HN

Just to make sure I understand your suggestion fully, you're saying: 1. Use `smallint` instead of `text` for the column type. 2. Otherwise follow our "`CHECK` constraint" approach (without it we're jeopardizing data consistency because we can store anything between -32768 and 32767 in that column). 3. Translate the int to the enum in your application.

Right?

Yep!

Most ORMs or just DALs can automate step 3 for you. If you’re using a strongly typed language with any type of ffi support, you’ll probably already have native cast from int to enum available (unless you’re using rust where that’s considered unsafe by default).

I don't like this approach much because getting cryptic integers when you do a `SELECT` in the database is really cumbersome.

Agreed, but on the other hand it also saves a lot of complexity and possible headaches down the line. It's kind of a matter of choosing which headache you want. Especially if your table gets larger all those extra bytes in text columns can cost you dozens of GB of disk space, makes indexing slower, etc.

I MySQL/MariaDB enums are just "aliases" for ints, and that works much nicer IMHO, and adding a new value is cheap because it doesn't recheck all the rows (removing values is still expensive, as it needs to check it's not actually used by any row).

Reply on news.ycombinator.com