Modeled 348 tables across 34 Postgres schemas
Modeled the maritime domain into 348 normalized tables across 34
PostgreSQL schemas — professionals, companies, ships, jobs, reviews
and the rest — with SMALLINT lookups and UUID v7 keys.
2025

Situation. The heart of the platform is a dense maritime domain —
professionals, companies, ships, jobs, reviews — and these things
reference each other constantly. A professional sails on ships,
works for companies, leaves reviews; a company owns ships and posts
jobs. Nearly every feature is a query across that web, so how well
the data is modelled decides how well most of the app performs and
how sane it is to extend.

Task. That domain had to be modelled so it stayed fast and kept its
integrity, and so that adding the next entity type didn't mean
fighting the schema.

Action. It's laid out as normalized PostgreSQL schemas organised by
domain — 348 tables across 34 of them by now. The many small, stable
enumerations — statuses, types, categories — became SMALLINT lookup
tables, which keeps the rows compact and the joins cheap instead of
storing text codes everywhere. Entities get UUID v7 primary keys, so
they're globally unique but still time‑ordered in the index. One
naming convention runs throughout — plural table names inside
singular‑named schemas — applied without exception, which as a bonus
sidesteps a lot of reserved‑word collisions. And the relationships
are held up by real foreign keys and constraints, so integrity is
the database's job, not something the application has to remember to
do.

Result. What came out is a data model that's consistent and quick,
and predictable to work in because the same rules hold everywhere —
there aren't special cases to memorise. Adding a feature usually
means extending the schema along the existing grain rather than
working against it, which is the only reason 34 schemas is a
structure rather than a sprawl. It's the floor the rest of the
platform stands on.

Categories:
1Platform Architecture	/categories/platform-architecture/	engineer.company	70
1Databases	/categories/databases/	engineer.company	70
1PostgreSQL	/categories/postgresql/	engineer.company	70
1SQL	/categories/sql/	engineer.company	70
1Data Engineering	/categories/data-engineering/	engineer.company	70
1Data Governance	/categories/data-governance/	engineer.company	70
1Performance Tuning	/categories/performance/	engineer.company	70

Services:
1Platform & Solution Architecture	/services/platform-architecture/	engineer.company	70
1Database Design & Modeling	/services/database-design/	engineer.company	70
1Data Governance & Quality	/services/data-governance/	engineer.company	70
1AI Data Readiness	/services/ai-data/	engineer.company	70

hThis page on the web	URL:https://engineer.company/portfolio/modeled-348-tables-across-34-postgres-schemas/	engineer.company	70
