I have seen project teams diving into developing a new application without having a basic diagram in place explaining any aspect of the solution -- including the data model.
Not sure what it takes for simple ER diagrams to become the norm for project documentation. In spite of so many tools being available in this space hardly anyone actually uses them in practice.
Maybe Microsoft has to make it a feature in Excel or PowerPoint for people to use it?
These diagrams need not be exhaustive but should convey the key structure and relations as per context. We can create a dozen small diagrams for the same database if needed to document the requirements and design. Whatever works and does the job of communicating and documenting.
A lot of developers even with multiple years of industry experience seem to start projects with code first -- as if it will be running on their PC.
Not enough systems / design thinking of how the application will run in a DEV environment (typically cloud these days) let alone production -- what the various components of that application are going to be -- and where and in form the data will be persisted.
As someone who starts with the data to try and understand a system, I agree but it feels like we're not the majority. At least in the MS world the rise of code-first has not been well received by me :(
I used to be bullish on creating ERDs, often using them as an onboarding exercise, even for databases with 100+ tables. However, comprehensive ERDs are becoming rare, and that's okay. Their value is short-lived due to the high cost of maintenance. While polished ERDs can be nice to have, they aren't essential.
For creating ERDs as code, tools like dbdiagram.io and eraser.io are popular options. ERWin is the original tool for UML/ERD diagrams, but it's expensive.
hand creating these seems like a mistake, i.e, like you said hard to maintain. In my opinion every database should generate (or have a tool) to generate diagrams automatically.
I've used tools like this to get my thoughts together around a design I have in my head. Personally I like DBML, since it gives me something I can pretty easily keep in source control and there are plugins for my editor.
Have you ever inherited a project with 80 tables and no documentation at all, or at best a 50 page document with database schema dumps?
You can draw it all on one page as an ERD (entity relationship diagram) with the main tables and their relationships. And this diagram can be understood in minutes, not hours/days like in the case of the schema dumps.
Also you can use various ERD editors, or text-based diagramming tools, like PlatUML, MermaidJS, DBML, on the schema design stage, while keeping the diagram sources in the source control.
Another usecase is to generate a detailed ERD for the current database schema in CI, and periodically review it, to ensure it conforms to the original design, and you understanding of the current schema is correct.
For me it makes it significantly easier to communicate with business and explain things like technical limitations and data requirements. It also helps with collaborative design on complex projects, making it clear where we're failing to meet data requirements
Same reason you might write out other specifications or maybe make a flow diagram of states something might go through in your application.
Certainly, with complex relations between various tables, having a design tool like this can help you visualize what you are doing. This in turn can help you in spotting things you might otherwise overlook.
Some of it depends on the sort of database work you do and how complex your database will be. If you only have a few simple tables with virtually no relations between them, this might indeed be overkill for you.
I use tools like a persistence layer for my thoughts. I can "think in words" but my brain defaults to "thinking in pictures" mode. When I think about a database schema I automatically visualize the tables and the connections between columns in a way similar to how most of these tools visualize the schema. My usage of these tools is more or less of me replicating what I see in my mind as a "save state" that I can resume in a day, month, or year.
I developed tools related to this, and have some guesses why some people see value and some don't:
1. A lot of the diagram-based modeling/visualization languages and tools are too simple, or poorly rendered, missing most of the potential value. (For example, for some product segments I knew, sold via enterprise sales, most of the offerings seemed to be developed by people who didn't use it themselves, and didn't have guidance from a domain expert.)
2. A lot of the examples people see of them are too simple, and often just plain wrong. (I was lucky to have a colleague explaining a complicated system by talking people through a rich diagram, and it was borderline epiphany. Then later I saw product managers and engineers make diagrams that were obviously incorrect/nonsensical, and otherwise weren't useful.)
3. I have a suspicion it might also sometimes involve some people having different "visual" or "spatial" thinking than others. Given a rich diagram in a familiar standardized visual language, do some people reason about it more visually than others, who are, say, reading it more like verbally, and reasoning from concepts that way?
It makes is so much easier to discuss things with other devs if there is even a scratch-level diagram of flow/data/api etc. Especially if it allows making comments, we use Figma at my company and for personal projects I use draw.io
many such offerings around, it seems. What I am looking for however, is a tool which I can use / script against. My use case is to produce (relatively straightforward) db diagrams from some model descriptions I have (part of another process). I have table names, column names, and relationships in a memory structure, and want to draw an ER like diagram. Currently looking at producing this wit plantuml, generating the puml file form my data, then running plantuml to generate the png / svg.
Looking around i find most tools in this corner are either full fledged DB design tools with their own editor, but no API. The others like this and things like dbdiagram.io usually are focused online only, which is not an option for me.
looking at it, it seems to be geared towards connecting to a database. My needs are to generate a DB like schema image from a custom in memory represntation of tables, columns and relations. Thanks for the tip though, it does seem like a useful tool, akin to plantuml in that it functions somewhat as a graphviz preprocessor iiuc.
I wish this existed when I started my project 5 years ago. I had to design a database schema for a full stack project using postgres. I was working with a scientific domain expert, and it was very helpful to have a diagram to review, so they could identify issues that might arise due to future domain specific needs.
This caused many one-on-one meetings where we would look at the diagram, and I'd note changes to be made for next meeting.
With this, we could have made changes live during meetings, and saved many iterations.
Things like pgadmin existed, but it didn't look as friendly for non-technical folks. This might decrease some friction.
Lovely, but my main problem with these tools is that they're unidirectional and aim to be the central authority. But they're a picture, no the database.
I understand why people uses them, I just don't need a tool like that.
So every tool "exports to SQL" expecting all changes in the database are reflected in the diagram. But the diagram is not the database. So we've got two jobs now.
dbeaver https://dbeaver.com/docs/dbeaver/ER-Diagrams/ does ERD. They are interactive and malleable. May not be "beautiful" but the whole product is FOSS and insanely useful for any database work.
I have definitely had issues getting the ERD to behave in dbeaver. For someone like me, it's fine. I'll fiddle until it works. For some folks I've worked with, not so much. I usually only want this sort of thing when collaboraring on design with those unfamiliar with SQL, but have domain knowledge (and arrive wielding excel spreadsheet the size of a small DB).
love it, but it doesn't (yet) handle editing huge tables with 100+ columns where the scroll bar jumps around a ton while trying to type. 100% going to be watching this project, and building smaller things with it
Thank you for the kind words! I'm glad you're enjoying ChartDB. We appreciate your feedback about handling large tables with 100+ columns. Improving that experience is definitely on our radar! It would be great if you could open an issue on our GitHub repo with more details, that way, we can ensure it gets the attention it deserves and make it even better for your use case. Looking forward to your input!
I think you should be able to select columns you want to show in the diagram, and hide the other behind a small ... Button. That way you can keep table box size small.
A default of "show none" is probably sane for visibility, in particular if you have many tables or columns in the whole diagram
sqlite does recognize the SMALLINT keyword and treats it as an INTEGER (sqlite only has two number types, integer and real). See here under "Type Affinity": https://www.sqlite.org/datatype3.html
Not sure what it takes for simple ER diagrams to become the norm for project documentation. In spite of so many tools being available in this space hardly anyone actually uses them in practice.
Maybe Microsoft has to make it a feature in Excel or PowerPoint for people to use it?
These diagrams need not be exhaustive but should convey the key structure and relations as per context. We can create a dozen small diagrams for the same database if needed to document the requirements and design. Whatever works and does the job of communicating and documenting.