mail@pastecode.io avatar
7 months ago
1.7 kB
UUID Data Type
For both the list_id and url_slug, the application will be using a UUID. There are a few different options for how we can store this in that database:

bigint with auto increment for the primary key, along with a unique constraint on a varchar(36) column for the value we will use for the id in the application

varchar(36)/varchar(32) primary key storing the plaintext UUID (with/without hashes)

binary(16) primary key storing the binary representation of UUID, and using UUID_TO_BIN function to get human readable value

can also have an auto-generated column for the human readable version with an index on it

API contracts in all scenarios will use a string for the list_id/url_slug, but there would be slight differences in how we query and insert data into the database depending on which solution is chosen.

We will be going with binary columns, along with generated virtual varchar(36) column for readability. All joins and where clauses will be on the binary column, while the select will return the human readable version. For example, in the url slug table, we would have the following:

id binary(16) not null,
slug_id varchar(36) generated always as (insert(insert(insert(insert(hex(id),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) virtual,
list_bin_id binary(16) not null,
list_id varchar(36) generated always as (insert(insert(insert(insert(hex(list_bin_id),9,0,'-'),14,0,'-'),19,0,'-'),24,0,'-')) virtual,
Any inserts will need to do the following:

unhex(replace(uuid(), '-', ''))
If you are selecting based on a UUID, it would be the same thing:

where id = unhex(replace('33384418-2740-11ee-881f-0242ac120004', '-', ''))