| I also use UUID's everywhere - primarily due to concurrency requirements - but I ended up writing a bunch of functions (`MACRO`s) that lookup UUIDs and join them to other tables where needed, instead of using VIEWs or JSON queries. I've not seen this approach documented much online - but it works really well for me. It has the advantage of keeping all my tables flat, while still being able to encode business logic into the database. A typical query I might type, would look something like.. `SELECT ,trip_start_date(id) FROM trips WHERE trip2region(id) = 'AU'` Where the two macros are just simple table joins, saving me the boilerplate work.
Where this approach has really shined, though, is through function composition... `SELECT FROM trips WHERE loc2region(trip_origin(id)) NOT IN loc2region(location_is_hotel(trip_locations_visited(id)))` So something like the above, which is a mix of scalar and table functions, would give me all
the trips where the traveler stayed in hotels outside of their home region.
Maybe not the best example, because I don't actually work with trip/hotel/travel data at all, but I'd be interested reading more about this approach. I was surprised how well-optimized the queries are.... \endyap |