CodeSOD: Joined Up

Sandra from InitAg (previously) works with Bjorn, and Bjorn has some ideas about how database schemas should be organized.

First, users should never see an auto-incrementing ID. That means you need to use UUIDs. But UUIDs are large and expensive, so they should never be your primary key, use an auto-incrementing ID for that.

This is not, in and of itself, a radical or ridiculous statement. I've worked on many a database that followed similar rules. I've also seen "just use a UUID all the time" become increasingly common, especially on distributed databases, where incrementing counters is expensive.

One can have opinions and disagreements about how we handle IDs in a database, but I wouldn't call anything a WTF there.

No, the WTF is how Bjorn would design his cross-reference tables. You know, the tables which exist to permit many-to-many relationships between two other tables? Tables that should just be tableA.id and tableB.id?

 Table "public.foo_bar" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+------------------------------------ id | integer | | not null | nextval('foo_bar_id_seq'::regclass) foo_id | integer | | not null | bar_id | integer | | not null | uuid | character varying(128) | | not null | 

Yes, every row in this table has an ID, which isn't itself a terrible choice, and a UUID, despite the fact that the ID of these rows should never end up in output anyway. It exists only to facilitate queries, not store any actual data.

I guess, what's the point of having a rule if you don't follow it unthinkingly at all times?

[Advertisement] Picking up NuGet is easy. Getting good at it takes time. Download our guide to learn the best practice of NuGet for the Enterprise.