4 ms·
That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UU
by eropple 9y ago
That's not a bad option, but I think I'd find locally-unique integer IDs spread across tables to be a little confusing. In that case I think I'd lean towards UUIDs, which may be a little easier overall, or use separate sequences but expose IDs via Hashids in my API/frontend: http://hashids.org http://hashids.org
- int_19h 9y agoIn Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.
- eropple 9y agoUUIDs are easier because they immediately scan as random values when you look at them. They are a clear indicator of surrogacy in a way that a spread-out sequence is not. It doesn't matter how difficult picking a sequence is (and your post is odd because I don't think I gave any indication of unfamiliarity with Postgres?). It's a cognitive concern with keys that look simple but are not obviously unrelated (as a normal sequence is by convention or UUIDs are by definition) to the rest of the table.
- WorldMaker 9y agoSure, but Postgres doesn't to my knowledge support a cross-table UNIQUE index to insure your sequences are followed in all cases. I imagine that all it takes is one confused junior developer to add a bunch of manually IDed data by hand to create some weird and confusing corner cases in this scheme.
- int_19h 9y agoYou can define the field as GENERATED ALWAYS, though, in which case specifying the value explicitly in INSERT won't work (there's an escape hatch, but it has to be explicitly and very verbosely invoked).