Random, but honest, question: Do other folks find DB diagrams to actually be useful?
I'm generally a very "visual" person, but I've never found DB diagrams to be helpful for me. The problem is that when you get an even mildly complicated schema, 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. I personally much prefer:
1. Just a simple, high-level textual description of the most important tables. Gives me a good "grounding" to start my investigation to understand the conceptual model.
2. Then, just a tree view of all the tables in a list, with their columns and indices underneath, basically the Database pane you get in DataGrip. It's very easy to see which columns are foreign keys so I can follow relationships to other tables if I want to, but it's not one giant, cluttered mass. I also always make use of Postgres COMMENTs on tables and columns, which will then display in DataGrip.
Don't mean to denigrate the team at all, and in fact another comment mentioned the same team did dbdocs.io, and a quick look at that site makes me think the "Table Structure" pane is very similar to what I've said I like above. Again, just curious if other folks find DB diagrams actually useful.
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.
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,...
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.
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.
> 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.
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.
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.
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.
While it looks really nice, hard to imagine it's quicker to hand draw this than reverse it out of your existing schema. I suppose if you are architecting something brand new then it can make some good sense to map it out quickly if you hate clicking and dragging your mouse around and want something version-controllable.
I would say, however, PlantUML is less pretty but more general as a tool and there are neat tools to make the diagrams directly from your database schema [0].
I've tried doing the SQL dump on dbdiagram a few months ago and ran into some issues. Unfortunately I don't remember specifically what, but after testing out a few other programs I ended up using the free version of https://dbschema.com/ and have been very happy with it.
I'm generally a very "visual" person, but I've never found DB diagrams to be helpful for me. The problem is that when you get an even mildly complicated schema, 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. I personally much prefer:
1. Just a simple, high-level textual description of the most important tables. Gives me a good "grounding" to start my investigation to understand the conceptual model.
2. Then, just a tree view of all the tables in a list, with their columns and indices underneath, basically the Database pane you get in DataGrip. It's very easy to see which columns are foreign keys so I can follow relationships to other tables if I want to, but it's not one giant, cluttered mass. I also always make use of Postgres COMMENTs on tables and columns, which will then display in DataGrip.
Don't mean to denigrate the team at all, and in fact another comment mentioned the same team did dbdocs.io, and a quick look at that site makes me think the "Table Structure" pane is very similar to what I've said I like above. Again, just curious if other folks find DB diagrams actually useful.