DbDiagram – Draw Entity-Relationship Diagrams, Painlessly

I find them helpful sometimes if they are diagrams given a domain context. Not just the whole DB and all its relationships. Mostly if the relationships are simple, and the naming is reasonable, it is mostly discoverable.

First thing that I do when starting a project is lay out the database. Before software was available I'd use a marker board, it helps to see everything laid out graphically. I literally cannot imagine doing it any other way. Recently I've been using MySQL Workbench but I've previously used Microsoft Access. I'd often wondered why no one had offered it as a SaaS product.

I find it useful to make them when starting something new just to get my thoughts down, but pretty useless to look at one that's remotely complex that someone else has made.

I think the best use case is when collaborating on the design with other devs, it's very easy to understand and to manipulate together during a meeting.

But also I found them useful as an onboarding tool: a single picture to describe the db, for eg. autogenerating them from the db FKs.

Granted, you can just look at the schema of each table, but having a single picture to look at will save some time and help memorizing the relationships.

You don't have to put all the tables in a single monstrous diagram. Tools will probably generate those but I usually provides several simpler ones in the documentation offering several perspectives e.g. authentication, acl/rbac,...

I used it multiple times.

I consider them essential.

Over the course of my career I've been pretty database centric in the work that I do. The databases I deal with will typically be highly normalized with table counts from around 300 on the low end and just over 1000 on the higher end. My experience is that they are helpful, though they can sometimes be a little overrated. I don't think I'd call them essential. Depending on the database vendor and the how the database was developed, if certain documentation was built into the database schema, some diagramming tools can pull this into the right places and so surface that documentation in way that can be more fluidly examined (think PostgreSQL's COMMENT ON functionality).

As others have pointed out, I usually don't use them to try to get the big picture: you're right in it is a morass of over-lapping lines and tiny boxes. At the whole database view, the best you can hope for is finding certain "information clusters", places where many references cluster, that can get you to the major ideas implemented by the application; this is helpful sometimes when coming to an application you haven't encountered before. The more useful aspect is when you've already got a central table and you're trying to understand the closest relationships to that table: maybe directly related or one away. There are database diagramming tools that will allow you to pick a relation and then limit the diagram to those one away, two away sets of relationships. Of course manually just telling the tool to diagram only a particular subject area of the database at a time reduces the sense of noise in these diagrams and typically you're only really trying to understand the data structure of such a subset of the database at any one time anyway.

The open source diagramming tool I use is called SchemaSpy (https://schemaspy.org/). Its not a database design GUI or anything like that. It inspects an existing database and creates documentation based on what it finds. It does that "pick a relation and show me relationships a couple degrees of separation" thing.

I used to work as part of a larger team in a fintech company before, and found db diagrams to be invaluable.

I'd generate the db diagram for the small set of tables which my PR is touching, and highlight/annotate the changed columns/constraints (using ksnip) and attach it in the PR. It significantly reduced the back-and-forth between DBA/managers and sped up approval times.

I have not found it useful to understand the table structure or domain model (which in our case could be very complex) but for explaining some scoped change/proposal to people who already had domain familiarity it proved to be very useful.

I guess interactive diagrams are better, mouse over an entity, and see which entities reference it/which entities it depends on.

E.g. mouse over a "Contact Information" and see "Person"/"Company" get highlighted as 2 entities that refer to "Contact Information"'s.

I find looking at example nested json objects for each entity useful containing all the joins. This is how you interact with the data anyway on the frontend. I’ll usually design a db this way.

> Random, but honest, question: Do other folks find DB diagrams to actually be useful?

Personally, yes, although TBH I haven't tried them with DBs have hugely wide tables or a high number of tables.

> things quickly become a morass of tons of intersecting relationship lines, and I just end up spending all my time trying to see how things are actually connected.

So look at a "sub-diagram" - diagram of some of the tables.

> a tree view of all the tables in a list,

Just think of the diagram as that tree view and choose an arbitrary order of visitation of the nodes, e.g. Left-to-Right then Top-to-Bottom.

> While it looks really nice, hard to imagine it's quicker to hand draw this than reverse it out of your existing schema

They have an option to take your SQL dump text and convert it back to DBML which is the DSL they use to render.

Auto generator for the prisms types? Can you share more about this setup? Was the generator something custom?