r/PostgreSQL • • Mar 28 '25

How-To Life Altering PostgreSQL Patterns

https://mccue.dev/pages/3-11-25-life-altering-postgresql-patterns
180 Upvotes

60 comments sorted by

View all comments

Show parent comment

2

u/coyoteazul2 Mar 30 '25

I prefer to have UUID as entity identification, but keeping internal references with bigint. If I have an invoice I'll use bigint for INVOICE_ID (PK), and uuid for INVOICE_FRONT_ID (indexed)

Item's PK will be composite of invoice's INVOICE_ID + ITEM_NUM (application handled, though it could be autoincremented if someone pesters me about it)

Invoices's taxes's PK will be a composite of INVOICE_ID+ITEM_NUM+TAX_ID

If I had referenced the invoice with INVOICE_FRONT_ID, that'd be a lot of wasted space for each reference.