DB design advice - shortcut foreignkeys vs long queries
The table structure you show looks fine from a database point of view, and it should work fine for an OLTP workload. Using nested loop joins, it is no problem to fetch a few rows by joining a number of tables.

If the schema is properly normalized, data modifications will be as localized as possible, and consistency is guaranteed.

Things become different when you want to perform analytical queries. Then joining many tables really hurts. This is why for OLAP workloads, you tend to denormalize the data and put them in a star schema or a similar structure. Such a &ldquo;data warehouse&rdquo; also usually doesn't hold the data in detail, but pre-aggregated in a way that makes the queries fast and the database not too large.

If you want to run both short transactions and analytical queries on the same database, it is hard or impossible to create a data model that fits both. Either you are ready to suffer with your analytical queries (possibly off-loading them to a streaming replication standby), or you create a second database with an analytical schema and an ETL process that regularly updates it from the live database.