Blog
ERD Best Practices and Database Design
The Schemity blog: database design for software engineers, from crow's foot notation and context views to reverse engineering a live database and keeping your ERD in Git.
Schemity's guide to turning a Postgres 0/1 integer flag into boolean: the USING clause that works on every width, the errors the cast hits,...
Schemity at 1,035 tables and 1,822 relations: what broke, how drawing, line jumps, dragging and export were redesigned, the measured cost, a...
Schemity's guide to naming Postgres constraints and indexes: the pattern to pick, the 63-byte cut that makes two names collide, and a query...
Schemity's guide to the Postgres view error on a column type change: find every view in the chain, what dropping one loses, and how long rea...
Schemity's guide to serial vs identity keys in Postgres: five ways serial misbehaves, tested on 18.3, and the 3 ms conversion that needs no...
Schemity's guide to what a database MCP server should let an AI agent do: why a read-only transaction is not a guard, and what to use when t...
SQLite has no ALTER COLUMN TYPE, so the fix is a table rebuild. Schemity's guide to the three things a rebuild quietly deletes, and the scri...
Schemity's guide to picking a Postgres foreign key's ON DELETE action: what each does to the child row, and the three ways SET NULL is accep...
A Postgres role with only USAGE on a schema sees every table, key and view definition but no rows. What to grant, what it still learns, and...
A NOT NULL column on a 5M-row Postgres table took 10 ms with a constant default and 10.4 s with a volatile one. The safe steps, and how Sche...
Use numeric for prices in PostgreSQL. Why a float sum changed on every run over 1M rows, what money gets wrong, and how Schemity shows what...
Generated column or trigger for a derived value in Postgres? What each costs on 1M rows, when only a trigger works, and how Schemity shows w...
In Postgres, adding a unique constraint blocks reads and writes; a unique index blocks writes. Which to use, and how Schemity flags the lock...
PostgreSQL cannot enforce a polymorphic commentable_id. Prefer a foreign key per parent or a supertype table; Schemity draws the polymorphic...
Let an AI agent write the migration, but review what it does to the schema rather than its SQL. Schemity draws an agent's migration file as...
A reverse engineered ERD shows every table and no domains, because the domains live in the code. Schemity lets an AI agent read that code, g...
On PostgreSQL, text and varchar(n) store strings identically, so a length limit is a rule, not an optimization. Schemity's impact analysis s...
Declare foreign keys wherever your database and migration tooling allow them; where they cannot, skip the enforcement but not the documentat...
Bigint when one database issues every id, UUIDv7 when something else does, and UUIDv4 as a primary key in neither case. Schemity's fk-type-m...
A column comment costs a full migration cycle, and on MySQL it means restating the entire column definition. Schemity keeps field descriptio...
NOT NULL foreign keys that point at each other accept no rows. Three ways to insert the first one, and how Schemity's lint finds every cycle...
timestamptz records an instant, timestamp records a reading on a clock, and your ORM probably chose the second one. Schemity's timestamp-not...
Schemity exports the diagram as a data dictionary in HTML, Markdown and Excel, covering every column, constraint and relationship, and close...
Migration linters read the statement you are about to run. Schemity's schema lint reads the model you already have - seventeen rules checked...
A unique constraint covers only the rows where every column in the key has a value. Sometimes that exemption is the design and sometimes it...
An array of IDs is a many-to-many relationship with no foreign key and no line on the diagram. Schemity renders PostgreSQL array types as re...
DrawSQL has no live database connection and caps tables on every plan. Schemity is the offline DrawSQL alternative that reverse engineers yo...
Schemity marks every workspace whose folder sits inside a Git repository with a branch icon, so which database diagrams are version controll...
Schemity deliberately has no zoom control. Every zoom changes the geography of the diagram and costs you the mental work of re-finding where...
Most database design software ignores database views or draw them as editable tables. Schemity renders views and materialized views as read-...
SSMS stores database diagrams as binary rows in sysdiagrams - no file, no Git, no export. Schemity reverse engineers SQL Server into a local...
Supabase Studio's Schema Visualizer shows one schema at a time and keeps your layout in one browser's localStorage. Schemity connects to the...
Referential actions decide which rows a single DELETE destroys, yet most database design software never store them and MySQL never logs them...
Native Postgres enums make every value-set change a locking migration, and no diagram tool shows what the allowed values are. Schemity rende...
Lucidchart has no live database connection - schema arrives as a hand-exported CSV. Schemity is the Lucidchart ERD alternative that reverse...
Staging and production schemas drift apart, and a text diff of two SQL dumps won't show you where. Schemity opens both environments as diagr...
Roles carry a tenant_id and permissions do not, and that one asymmetry decides the whole design. How the roles, permissions, and junction ta...
A relational database cannot store a many-to-many relationship directly - it needs a junction table holding one foreign key per parent, whic...
database design software misdraws 1:1 relationships as 1:N because they ignore unique constraints. Schemity derives crow's foot cardinality...
Schemity's Context Map derives a DDD context map from the schema itself: each context view becomes a node, and every dependency arrow is bac...
Raster ERD exports blur, crop, or balloon in size. Schemity, database design software, exports crisp SVG or Mermaid text of the whole diagra...
Schemity attaches markdown descriptions to entities, legends, and context views, so what your tables mean lives inside the diagram - not in...
A DbSchema alternative at less than half the price: Schemity is database design software with JSON-in-Git storage and an MCP server for AI a...
Inherited a huge undocumented database? A full-schema poster is unreadable by design. Reverse engineer it into database design software, the...
A real workflow for a 60-entity schema: design the whole model up front, implement it context by context with AI-generated code, then let re...
ERDs go stale the moment the schema moves on, and re-importing destroys your layout. Schemity re-syncs the diagram from the live database ev...
Most diagram tools only let your schema out as a picture or bare DDL, so years of work stay trapped. Database design software with DBML and...
Free-tier database design software caps you at 15 tables or 60 objects and paywall private diagrams, so the pricing page ends up shaping you...
When relationship lines cross, most schema visualizers make you guess which one you were following. Schemity - database design software with...
Connect a local-model MCP host to Schemity's MCP server for private AI database design with zero cloud calls - for teams where cloud AI is b...
Supabase Studio draws your schema but keeps the layout in one browser's localStorage; pgAdmin's exports follow the UI theme. Database design...
Millions of developers query information_schema just to find which table holds a column. Database design software with fuzzy search across e...
Cloud diagram tools can lose your ERD - one day it is just gone from the list, and recovery means a support ticket or a paid tier. Database...
A ChartDB alternative that runs offline: Schemity is database design software with an MCP server for AI agents, diagrams stored as JSON in G...
Schema drift starts at the blank entity. How entity templates and convention-aware field placement in database design software keeps every t...
How to document a production database schema without fear: database design software that connects through SSH tunnels, keeps passwords in yo...
database design software for consultants and client work: one workspace per client, stored as local folders you can hand over or delete when...
Generate an ERD from a SQL dump, import CREATE TABLE statements in one paste, and turn diagram edits into a reviewed migration SQL diff - al...
Private AI database design over MCP: Claude Code or Cursor reads your schema through Schemity on your machine and stages edits you review. N...
Low coupling and high cohesion are two questions at two scales. See why the main ERD perspective and a database context view answer differen...
Database context views and ERD perspectives give focus by removing noise - but they also take. Here is what a sub-diagram in your database d...
A bar means one, a crow's foot means many, a circle means zero is allowed. This guide maps every crow's foot symbol in an ER diagram to the...
The complete multi-tenant RBAC schema is seven tables: tenants, users, members, roles, permissions, and two junction tables. Here is the ful...
dbdiagram.io's free tier is 10 public diagrams, then $14/month. Schemity is the offline dbdiagram.io alternative: private by default, diagra...
Most database design software treats schema work as a chore. What if database design software got out of the way - with ERD custom waypoints...
Import a workspace from anywhere on your machine and treat your ERD like infrastructure - version-controlled in Git, reviewed in PRs, and ke...
ERD check constraints support turns your schema into living documentation. See how domain-driven design database schema patterns surface val...
The best ideas happen fast. Most database design software makes you slow down. Database design software built for the speed of thought - key...
Stop exporting PNGs to Confluence. Database design software stores your database diagrams as JSON files you can version control, review in P...